Must declare the scalar variable - help required

Question

Post reply

Must declare the scalar variable - help required

isaac.a

SSC Enthusiast

Points: 164
More actions
January 24, 2011 at 6:39 am

#232619

begin
DECLARE @deldate varchar(10)='01/01/1960',
@tblname varchar(50) = 'CACCOUNTSTMT',
@ssql varchar(200)
set @ssql = 'set @deldate = (select max(Convert(varchar(10) ,RowCreateDate,103)) from ' +@tblname +')'
print @ssql
--set @deldate = @ssql
exec(@ssql)
print 'deledate'
print @deldate
end
gives the following error
Must declare the scalar variable "@deldate".
can any one help on this?

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

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

David Burrows SSC Guru Points: 65142 More actions · Answer 1

try

exec sp_executesql @ssql,N'@deldate varchar(10) OUTPUT',@deldate OUTPUT

instead of

exec(@ssql)

Far away is close at hand in the images of elsewhere.
Anon.

WayneS SSC Guru Points: 95460 More actions · Answer 2

isaac.a (1/24/2011)
begin
DECLARE @deldate varchar(10)='01/01/1960',
@tblname varchar(50) = 'CACCOUNTSTMT',
@ssql varchar(200)
set @ssql = 'set @deldate = (select max(Convert(varchar(10) ,RowCreateDate,103)) from ' +@tblname +')'
print @ssql
--set @deldate = @ssql
exec(@ssql)
print 'deledate'
print @deldate
end
gives the following error
Must declare the scalar variable "@deldate".
can any one help on this?

In the dynamic sql, you are setting a variable that doesn't exist in the context of the dynamic sql connection.

Use the solution David supplied to pass it in and get the results back out.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

isaac.a SSC Enthusiast Points: 164 More actions · Answer 3

isaac.a

SSC Enthusiast

Points: 164

January 24, 2011 at 9:26 pm

#1277595

Thank u so much. It works.