I am trying to write a simple stored process that takes three arguments "database name one", "database name two" and "table name". Then sql will do a row count for a specific table in each database and save it.
Working on it in parts, I ran into the first problem that you cannot do
select * from @tablename
I know that you can use dynamic sql with the exec command, but this is not ideal since I cannot return the values.
The following example looks as if it should work, but does not work.
declare @tablename as nvarchar(500) declare @sqlstring as nvarchar(500) declare @parmdefinition as nvarchar(500) declare @numrows as bigint set @tablename = N'dummy_customer' set @parmdefinition = N'@tablenameIN nvarchar(500), @numrowsOUT as bigint OUTPUT' select @sqlstring = 'select @numrowsOUT = count(*) from @tablenameIN' select @sqlstring exec sp_executesql @sqlstring, @parmdefinition, @tablenameIN = @tablename, @numrowsOUT = @numrows OUTPUT select @numrows
The specified error message
Msg 1087, Level 16, State 1, Line 1 Must declare the table variable "@tablenameIN".
Currently using SQL Server 2008 SP2.
Edit: We do this because we are doing the migration, and the client wants to get a report that shows the number of rows for each table in the source and target database. Since there are many tables that can use sp_MSForEachTable to call a stored proc, it seems ideal.
Edit:
The ultimate solution for future use.
declare @tablename as nvarchar(500) declare @sqlstring as nvarchar(500) declare @parmdefinition as nvarchar(500) declare @numrows as bigint set @tablename = N'dummy_customers' set @parmdefinition = N'@tablename nvarchar(500), @numrowsOUT as bigint OUTPUT' select @sqlstring = 'select @numrowsOUT = count(*) from ' + quotename(@tablename) exec sp_executesql @sqlstring, @parmdefinition, @tablename = @tablename, @numrowsOUT = @numrows OUTPUT select @numrows