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
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;
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)
