-- general syntax SELECT count(*) NoRecords FROM <schema>.<table> -- example SELECT count(*) NoRecords FROM [Person].[Address]
Some prefer using a count(1) instead of a count(*), though it doesn't make a difference considering how SQL Server handles the two scripts in the background.
-- general syntax SELECT count(1) NoRecords FROM <schema>.<table> -- example SELECT count(1) NoRecords FROM [Person].[Address]
Same can be achieved by using a SUM:
-- general syntax SELECT SUM(1) NoRecords FROM <schema>.<table> -- example SELECT SUM(1) NoRecords FROM [Person].[Address]
However the SUM is typically used when counting only certain records within the same line (the attributes must be know however beforehand):
-- counting the occurrences for a certain values via SUM SELECT SUM(CASE WHEN Class IS NULL THEN 1 ELSE 0 END) [Nulls] , SUM(CASE WHEN Class = 'H' THEN 1 ELSE 0 END) [H] , SUM(CASE WHEN Class = 'L' THEN 1 ELSE 0 END) [L] , SUM(CASE WHEN Class = 'M' THEN 1 ELSE 0 END) [M] , count(*) NoRecords FROM [Production].[Product]
The sane output can be used via a GROUP BY based on the grouping attributes:
-- counting the occurrences for a certain attribute via COUNT SELECT Class , count(*) NoRecords FROM [Production].[Product] GROUP BY Class -- counting the occurrences for a group of attributes via COUNT SELECT Class , Color , count(*) NoRecords FROM [Production].[Product] GROUP BY Class , Color
If you want to implement the functionality in a function or stored procedure and provide the name of the table as parameter, then things get a little more complicated as the query needs to be built dynamically on runtime. In stored procedures a query built dynamically can be usually executed using EXEC or EXECUTE stored procedures, though none of them will do as we need to reuse the actual value returned by the query. This can be achieved with the help of a third stored procedure called sp_executesql, and the work can be done relatively easily with the below stored procedure:
-- dropping the stored procedure --DROP PROCEDURE IF EXISTS dbo.pGetNumberRecords -- creating the stored procedure CREATE PROCEDURE dbo.pGetNumberRecords( @Table varchar(100) , @NumberRecords int OUTPUT ) AS DECLARE @SglQuery nvarchar(250) IF IsNull(@table,'')<>'' BEGIN SET @SglQuery = N'SELECT @NumberRecords = count(*) FROM ' + @table --get the number of records EXEC sp_executesql @Query = @SglQuery , @params = N'@NumberRecords INT OUTPUT' , @NumberRecords = @NumberRecords OUTPUT END -- testing the stored procedure DECLARE @NumberRecords int EXEC dbo.pGetNumberRecords '[Production].[Product]', @NumberRecords OUTPUT SELECT @NumberRecords
The overhead of using a stored procedure for getting the records count is bearable though the solution is not necessarily elegant, a nicer approach being the use of functions for the same purpose:
-- dropping the function --DROP FUNCTION IF EXISTS dbo.GetNumberRecords -- creating the test function CREATE FUNCTION dbo.GetNumberRecords( @table as nvarchar(100)) RETURNS int AS BEGIN DECLARE @sql varchar(200) DECLARE @NumberRecords int IF IsNull(@table,'')<>'' BEGIN SET @sql = N'SELECT @NumberRecords = count(*) FROM ' + @table --get the number of records EXEC sp_executesql @Query = @sql , @params = N'@NumberRecords INT OUTPUT' , @NumberRecords = @NumberRecords OUTPUT END RETURN @NumberRecords END -- testing the function SELECT dbo.GetNumberRecords('[AdventureWorks2014].[Production].[Product]')
Unfortunately UDFs have the restriction that they can’t execute stored procedures, and thus the above technique can’t be used. Even if the function is created, when attempting to run it, the following error occurs:
Msg 557, Level 16, State 2, Line 185
Only functions and some extended stored procedures can be executed from within a function.
There is actually an alternative for executing pass-through queries in functions by using OPENQUERY, OPENROWSET or OPENDATASOURCE functions, allowing thus to execute DML statements against a given linked server, as is the case of first two functions, respectively using ad-hoc connection information as is the case of the third function. For exemplification I chose OPENQUERY function, this being the code created to return the number of records from a specified table (you'll need to replace
--dropping the test function -- DROP FUNCTION IF EXISTS dbo.GetNumberRecordsTest -- creating the test function CREATE FUNCTION dbo.GetNumberRecordsTest() RETURNS int AS BEGIN RETURN ( SELECT NoRecords FROM OPENQUERY( <server_name>, 'SET NOCOUNT ON; SELECT count(*) NoRecords FROM [AdventureWorks2014].[Production].[Product]') AS O) END -- retrieving the number of records SELECT dbo.GetNumberRecordsTest()
For this piece of code to work is first necessary to refer the server as a linked server:
EXEC sp_serveroption <server_name>, 'Data Access', true
Withought doing this first you’ll get the error:
Msg 7411, Level 16, State 1, Procedure GetNumberRecords, Line 4 Server '>server_name<' is not configured for DATA ACCESS.
You can use sp_helpserver to get server's name:
-- getting server's name EXEC sp_helpserver
The above function doesn’t help much. It would be helpful if we could provide the table’s name as parameter and store the query string in a variable, something like:
-- creating the test function CREATE FUNCTION dbo.GetNumberRecords( @TableName as nvarchar(100)) RETURNS int AS BEGIN DECLARE @sql varchar(200) SET @sql = 'SET NOCOUNT ON; SELECT count(*) NoRecords FROM ' + @TableName RETURN ( SELECT NoRecords FROM OPENQUERY(, @sql) AS O) END -- retrieving the number of records SELECT dbo.GetNumberRecords()
Unfortunately OPENQUERY function same as OPENROWSET and OPENDATASOURCE expect a constant parameter as query and therefore the error (as per SQL Server 2005):
Msg 102, Level 15, State 1, Procedure GetNumberRecords, Line 12 Incorrect syntax near '@sql'. Expecting STRING or TEXT_LEX.
I wonder why the SQL Server team hasn’t allowed the use of variables as parameters for OPENQUERY functions, if not in the previous SQL Server versions at least in SQL Server 2008! Anyway, another impediment would be raised by the deterministic nature of functions.
2 comments:
Hola Buen dia para pasar parametros en la clausula where solo hay que utilizar un ejemplo como este
SELECT Responsable,Empleado From OpenQuery(WEBURREA,'Select rr.Responsable As Responsable,Empleado From dhdbD..relacionempleadoresponsable rr')Where Empleado=@Empleado
Donde @Empleado es tu variable a pasar.
Por Carlos Ruiz de Guadalajara
OPENQUERY Pasar Parametros Solucionado.
Hola Buen dia para pasar parametros en la clausula where solo hay que utilizar un ejemplo como este:
SELECT Responsable,Empleado From OpenQuery(WEBURREA,'Select rr.Responsable As Responsable,Empleado From dhdbD..relacionempleadoresponsable rr')Where Empleado=9321
Por Carlos Ruiz de Guadalajara
Post a Comment