April 17, 2007 at 3:35 pm
I need to place the record count of a table in a numeric variable. The problem is the the table name is in a variable also.
declare @records int, @tablename varchar(50)
set @records = (select count(*) from @tablename) doesn't work.
I've tried every combination of set, select, execute, & string commands I can think of and nothing has worked so far.
Suggestions, please!
April 17, 2007 at 3:52 pm
Well, the piece that's causing you problems here is your table name. You cannot use a variable in the FROM clause of a DML statement. So your existing syntax would work fine if the table name were static. If you must store your table name in a variable, you'll be forced to use dynamic SQL in the form of EXEC('sql select string here') or sp_executeSQL 'sql select string here'. The problem you now have is that you cannot assign the results of either one of these methods into a variable. I would suggest making a stored procedure where you will execute the dynamic sql and have the procedure return the row count into a variable in the form of:
EXEC @Records = proc_Call_Your_StoredProcedure @TableName
April 17, 2007 at 3:52 pm
declare @records int, @tablename varchar(50), @SQL nvarchar(4000)
SET @SQL = 'SELECT @rec = count(*) from dbo.[' + @tablename + ']'
exec sp_executesql @SQL, N'@rec int OUTPUT', @rec = @records OUTPUT
_____________
Code for TallyGenerator
April 17, 2007 at 5:55 pm
Thanks Sergiy......that worked just great!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply