Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
Use sp executesql Or EXEC To Get The Count For Dynamic Table Names
From Wiki
You need the row count for a table but this table could change every day or change based on which user executes the stored procedure
There is no point in creating dozens of stored procedures, all this can be done by using exec (sql) or sp_executesql
Of course sp_executesql is much better and if you run the statements below you will also see that it's execution plan is only 29.04 percent relative to the batch
So let's get started.
- USE pubs
- GO
- --sp_executesql
- DECLARE @chvTableName VARCHAR(100),
- @intTableCount INT,
- @chvSQL NVARCHAR(100)
- SELECT @chvTableName = 'Authors'
- SELECT @chvSQL = N'SELECT @intTableCount = COUNT(*) FROM ' + @chvTableName
- EXEC sp_executesql @chvSQL, N'@intTableCount INT OUTPUT', @intTableCount OUTPUT
- SELECT @intTableCount
- GO
- --EXEC (SQL)
- DECLARE @chvTableName VARCHAR(100),
- @intTableCount INT,
- @chvSQL NVARCHAR(100)
- CREATE TABLE #temp (Totalcount INT)
- SELECT @chvTableName = 'Authors'
- SELECT @chvSQL = 'Insert into #temp Select Count(*) from ' + @chvTableName
- EXEC( @chvSQL)
- SELECT @intTableCount = Totalcount from #temp
- SELECT @intTableCount
- DROP TABLE #temp
First Hit CTRL + K (this will show the execution plan) , then highlight the complete code, hit F5 and look at the Execution Plan tab
As you can see sp_executesql is more than twice as efficient as exec (SQL)
Contributed by: --SQLDenis 13:19, 5 August 2008 (GMT)
Part of SQL Server Programming Hacks
Section Handy tricks
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.