Assigning Variables using DYNAMIC T-SQL

Question

Post reply

Assigning Variables using DYNAMIC T-SQL

Mark Ivey

Newbie

Points: 8
More actions
January 9, 2008 at 8:22 am

#202207

Hey guys,
I have a problem trying to figure out how to assign and variable value using dynamic T-SQL.
I need to retrieve a value of row counts from a table that is created on the fly using T-SQL and I can't for the life of me figure out the best way to do it.
Here is the code:
EXEC('SELECT count(*) FROM ' + @newTableName + ')
What I am trying to do would seem to be fairly simple but I need to take the value of the statement and assign it to a record variable something like:
DECLARE @record_ct = EXEC('SELECT count(*) FROM ' + @newTableName + ')
but this won't work for obvious reasons and this:
SELECT @row_count= count(*) FROM @newTableName
doesn't work for even more obvious reasons.
I need the value captured in a variable to do something like this:
if(@record_ct > 1000)
begin
// blah blah blah
end
Can anyone here help me out??? I would really appreciate any input. 🙂

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Loner SSC-Insane Points: 21279 More actions · Answer 1

declare @RECORDCNT int

declare @TABLE char(100)

declare @cmd Nvarchar(100)

-- Set the table to be used

set @TABLE = '[ORDERS]'

-- Build the dynamic T-SQL command

SET @CMD = 'select @RECORDCNT=count(*) from ' + @TABLE

-- Call the sp_executesql SP to return the

-- record count for @TABLE variable

exec sp_executesql @CMD,N'@RECORDCNT int out,

@TABLE char(100)',@RECORDCNT out , @TABLE

-- Display the number of records

print @RECORDCNT

Mark Ivey Newbie Points: 8 More actions · Answer 2

You're the man Loner. Thanks for the code example and direction, I'll definitely make note of this for the future. 😉

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

actually - SHE's the woman...:) But I'm sure she'll appreciate the feedback...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?