CODEWITHSUNDEEP
t-sqlvariablesexec
Sara N
Sara N

Reputation: 1189

execute TSQL command with an output variable

I want to pass an SQL command to a variable and execute aggregate function for that. but when I run it, this error is shown, and nothing appear for @cnt value ,

Must declare the variable '@cnt'

What's my mistake ? 

DECLARE @ret varchar(300);
set @ret = '';
declare @cnt int;
set @ret = 'select @cnt = count(*) from TBL1'
EXEC (@ret)
print @cnt

Upvotes: 0

Views: 266

Answers (2)

Lee
Lee

Reputation: 189

You could use sp_executesql to access a variable inside of your dynamic SQL String:

DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @cnt varchar(30);

SET @SQLString = N'SELECT @cntOUT = count(1) from tbl1';
SET @ParmDefinition = N'@cntOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition,  @cntOUT=@cnt OUTPUT;
SELECT @cnt;

Upvotes: 1

Greg the Incredulous
Greg the Incredulous

Reputation: 1836

The exec statement signals the end of a batch, so the print statement doesn't know about @cnt.

Try this instead:

DECLARE @ret varchar(300)
set @ret = ''

set @ret = 'declare @cnt int
select @cnt = count(*) from [Load].RINData
print @cnt'
EXEC (@ret)

Upvotes: 0

Related Questions