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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy