About Me

IT Professional with more than 16 years experience in IT especially in the area of full life-cycle of Web/Desktop Applications Development, Database Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP support, etc.

Saturday, September 12, 2009

The Number of Records

    Getting the number of records from a table is straightforward, a simple select doing the job:

SELECT count(*) NumberRecords
FROM <your_table>

Actually it’s advisable to use count(1) instead of count(*):

SELECT count(1) NumberRecords
FROM <your_table>

    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:
    The overhead is relatively minimum but not necessarily elegant, a nicer approach being the use of functions for the same purpose. Unfortunately UDFs have the restriction that they can’t execute stored procedures, and thus the above technique can’t be used. 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:
    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.


    The above function doesn’t help too 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:
Unfortunately OPENQUERY function same as OPENROWSET and OPENDATASOURCE are expecting a constant parameter as query and therefore the error:

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:

Carlos Ruiz said...

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

Carlos Ruiz said...

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