Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Must declare the scalar variable - help required Expand / Collapse
Author
Message
Posted Monday, January 24, 2011 6:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 8:25 AM
Points: 8, Visits: 7
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?
Post #1052323
Posted Monday, January 24, 2011 6:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:47 AM
Points: 7,051, Visits: 6,815
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.

Post #1052324
Posted Monday, January 24, 2011 9:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 6,582, Visits: 8,861
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1052457
Posted Monday, January 24, 2011 9:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 8:25 AM
Points: 8, Visits: 7
Thank u so much. It works.
Post #1052841
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse