Recently I was asked to write a T-SQL code to count the rows of any table, given its name. My first attempt was to embed an EXECUTE(…) statement in the body of a stored procedure, and get the table name as an input parameter:
CREATE PROC dbo.RecordCount(@tablename nvarchar(128)) AS EXECUTE('SELECT COUNT(*) FROM '+@tablename)
The solution was straightforward, but what if you wanted the record count as an output parameter? You cannot use EXECUTE since it doesn’t support parameters. In order to both execute a dynamic statement and support parameters, you have to use the system stored procedure sys.sp_executesql, which has a similar behavior to EXECUTE.
After reading the MSDN documentation I wrote the following code:
CREATE PROC dbo.RecordCount @TableName nvarchar(128), @Count int OUTPUT AS BEGIN DECLARE @SQLString nvarchar(500); DECLARE @ParamDefinition nvarchar(500); SET @SQLString = 'SELECT @Count = Count(*) FROM '+@TableName SET @ParamDefinition = '@Count int OUTPUT' EXECUTE sys.sp_executesql @SQLString, @ParamDefinition, @Count output END
As you can see, the dbo.RecordCount stored procedure declares an input parameter called @TableName and an output parameter called @Count; the interesting part comes with the body of the stored procedure. At first I build the dynamic SQL statement and store it in the @SQLString variable. Then I build a second SQL statement, declaring the parameter used inside @SQLString; I store it in the @ParamDefinition variable. Finally I invoke EXECUTE sys.sp_executesql passing it three parameters: my two string variables (the dynamic statement and the definition of @Count OUTPUT) and my actual procedure OUTPUT parameter, which was described inside @ParamDefinition in order to be recognized by sp_executesql.
Using this function is easy as declaring a variable and reading an output parameter. For instance, you can define it inside AdventureWorks2008R2 database and test it with the Person.Person table:
DECLARE @c int EXEC RecordCount 'Person.Person',@c OUT PRINT @c
What’s worth remembering is that one of the advantages of sp_executesql compared to EXECUTE, is that SQL Server is able to cache the execution plan of the dynamic query so that subsequent invocations of the procedure are likely not recompiled (you can read more on MSDN.)