Help with variable declaration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with variable declaration

Author

Topic

tinman
Starting Member

2 Posts

Posted - 2002-09-19 : 14:41:53

Can anyone help me understand why the following code
gets a complaint about @CarDollars not being Declared?

I can cut the results of the print statement into
Query Analyzer and it runs fine with the same
declarations you see below.

SQL 2000 on Win 2000

-----When I run this code------

Declare @DK varchar(12)
Declare @BegDate varchar(12)
Declare @EndDate varchar(12)

Select @DK = '3'
Select @BegDate = '3/1/02'
Select @EndDate = '4/1/02'

Declare @CarDollars varchar(20) --<<<<<<

Declare @Stmt varchar(2000)

Select @Stmt = 'SELECT
@CarDollars = Sum(Case RecType When ''Car'' Then TotalSale Else 0 End)
From PartnerDKTables.dbo.[' + @DK + ']
Where CreationDate >= ''' + @BegDate + ''' And CreationDate < ''' + @EndDate + ''''

Print @Stmt

Exec (@Stmt)

-----I get the following results------

SELECT
@CarDollars = Sum(Case RecType When 'Car' Then TotalSale Else 0 End)
From PartnerDKTables.dbo.[3]
Where CreationDate >= '3/1/02' And CreationDate < '4/1/02'
Server: Msg 137, Level 15, State 1, Line 3
Must declare the variable '@CarDollars'.<-------This variable has been declared

Any help is appreciated.
Thanks
Jeff

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-09-19 : 14:52:57

You Declared @CarDollars OUTSIDE of your Dynamic SQL.

Try this:


Select @Stmt = '
DECLARE @CarDollars VARCHAR(20)

SELECT @CarDollars = Sum(Case RecType When ''Car'' Then TotalSale Else 0 End) 
FROM PartnerDKTables.dbo.[' + @DK + '] 
WHERE CreationDate >= ''' + @BegDate + ''' And CreationDate < ''' + @EndDate + ''''

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-09-19 : 15:05:14

sp_executesql works good for these types of things allowing you to sort of bind to dynamic sql parameters values and output parameters... look it up in BOL

tinman
Starting Member

2 Posts

Posted - 2002-09-19 : 17:33:42

Hi Guys,

I'm still striking out.
If I put the declaration in the @Stmt string,
it prints the string with out complaining.
But, it does not return a result for @CarDollars.

My reading about sp_executesql in BOL seems to be telling
me that what I trying to do, can't be done.
Check out the topic "Using sp_executesql",
under "Self-contained Batches" after the bulleted
items check out the first example.

I hit something like this before and the work
around was to use a temp table.

Something like this:
Declare @Tmp money

Create Table #TmpValue
(Value money)

If @Result = 'P'
Begin
Exec ('Insert #TmpValue
(Value)
Select Sum(TotalSale)
From PartnerDKTables.dbo.[' + @DK +']
Where CreationDate >= ''' + @BegDate + ''' And CreationDate < ''' + @EndDate + ''' And CancelDate is null')

Select @Tmp = (Select * From #TmpValue)

Thanks for taking a shot at it.

Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources