Help with variable declaration

[Guest_imported]

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

 

[rac2]

Hello Jeff,

I think I have run into this problem myself. If I remember correctly this is the situation. The statements inside the EXECUTE() do not have access to the variables declared in the procedure. You can test this by adding the DECLARE and SET statements to @Stmt. This should eliminate the error. Likewise the value assigned to @CarDollars will not be available outside of the EXECUTE() so you must add
SELECT @CarDollars to @Stmt. I think this will give you the resulting value of @CarDollars as an output of the stored procedure. But how you would get that value into an output variable, or for use elsewhere in the procedure, I don't know.

It may be that SQL 2000 has a special datatype for system objects or at least for table names so that you can construct the table name and use it in the SELECT statement without using EXECUTE(). But this is all specific to MS SQL Server, not ANSI SQL, so you might want to post your question there. There are some true experts in that forum who will know what to do.

Regards,
Richard