dynamically generated SQL -- can''t get @@rowcount!!
-
the code block is living inside a sproc that gets run nightly. I need to verify that at least some number of records were inserted, but I can't figure out how to get the @@rowcount from the "select into" statement below.
I've been playing around with "exec", but scope seems to be an issue.
SET @cmd = '
SELECT*
INTO[mail_bounceback_' + CONVERT(varchar(10), @today, 112) + ']
FROMmail_bounceback
DROP TABLE [mail_bounceback_' + CONVERT(varchar(10), @today-14, 112) + ']
'
EXEC (@cmd)
-
I think this does what you want
SET @cmd = 'declare @rows int; ' +
'SELECT * INTO [mail_bounceback_' + CONVERT(varchar(10), @today, 112) + '] ' +
'FROM sysprocesses; ' +
'select @rows = @@rowcount; '+
'DROP TABLE [mail_bounceback_' + CONVERT(varchar(10), @today-14, 112) + ']; ' +
'select @rows as qty_affected'
EXEC sp_executesql @cmd
-
thanks Mike, but this yields the scoping issue I can't get around. I need to be able to do something with the value of @@ROWCOUNT, so I can't just send it to standard output.
declare @cmd varchar(512)
SET @cmd = 'declare @rows int ' +
'SELECT * INTO zzzgregtemp ' +
'from emailencrypt ' +
'select @rows = @@rowcount'
EXEC (@cmd)
select @rows
----------------------------YIELDS:------------------
Server: Msg 137, Level 15, State 2, Line 10
Must declare the variable '@rows'.
How would I get that standard output into a local variable?
-
Read BOL about sp_executesql, especially the part about using output parameters.
_____________
Code for TallyGenerator -
Thanks Sergiy, but nothing in the 4 subtopics under sp_executesql in my BOL about "output" params
Here's what I've fabricated so far and it's not working:
DROP TABLE zzzgregtemp
GO
DECLARE @paramdef NVARCHAR(128)
DECLARE @cmd NVARCHAR(512)
DECLARE @a INT
SET @cmd = N'SELECT * INTO zzzgregtemp ' +
'from emailencrypt ' +
'select @numrows = @@rowcount'
SET @paramdef = N'@numrows INT OUTPUT'
EXECUTE sp_executesql @cmd,
@paramdef,
@a = @numrows OUT
SELECT @a
-
NEVERMIND! I GOT IT!!!
I had to switch the output param name with the local variable:
DECLARE @paramdef NVARCHAR(128)
DECLARE @cmd NVARCHAR(512)
DECLARE @a INT
SET @cmd = N'SELECT * INTO zzzgregtemp ' +
'from emailencrypt ' +
'select @numrows = @@rowcount'
SET @paramdef = N'@numrows INT OUTPUT'
EXECUTE sp_executesql @cmd,
@paramdef,
@numrows = @a OUT
SELECT @a
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply