Set variable error

johnnyg123
johnnyg123 used Ask the Experts™
on
I have the following in a stored procedure

(Trying to capture value in a variable so can read it from a vb.net app)


DECLARE @TableTierCreditsMonth1 as Decimal(18,2)
DECLARE @TableTierCreditsMonth2 as Decimal(18,2)
DECLARE @TableTierCreditsMonth3 as Decimal(18,2)


;WITH TempTableTierCreditsMonth1 (Playerid,TableTheoMonth1, TablewinMonth1, TableTierCreditsMonth1)
AS

(SELECT PlayerID
            ,sum(Table_TheoWin) AS TableTheoMonth1
            ,sum(Table_Win) AS TablewinMonth1
            ,case when sum(Table_TheoWin) >= sum(Table_Win) * @TableTierMultiplier then sum(Table_TheoWin) else sum(Table_Win) * @TableTierMultiplier end TableTierCreditsMonth1
            
      FROM CopyPlayerManagement..PlayerDay
      WHERE AccountingDate BETWEEN @BeginDateMonth1
                  AND @endDateMonth1
            AND Table_Games > 0
            and playerid = @PlayerID
      GROUP BY PlayerID)
      
            
      set @TableTierCreditsMonth1 = (Select TableTierCreditsMonth1 from TempTableTierCreditsMonth1)

I get an error saying Incorrect syntax near the keyword 'SET'

Not sure why
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
You have to have a SELECT statement after a CTE:

SELECT @TableTierCreditsMonth1 = TableTierCreditsMonth1 from TempTableTierCreditsMonth1
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
I agree with above comment, the SET alone won't work, as it "forgot" the CTE already.

this will work better:
DECLARE @TableTierCreditsMonth1 as Decimal(18,2)
DECLARE @TableTierCreditsMonth2 as Decimal(18,2)
DECLARE @TableTierCreditsMonth3 as Decimal(18,2)


;WITH TempTableTierCreditsMonth1 (Playerid,TableTheoMonth1, TablewinMonth1, TableTierCreditsMonth1)
AS

(SELECT PlayerID
            ,sum(Table_TheoWin) AS TableTheoMonth1
            ,sum(Table_Win) AS TablewinMonth1
            ,case when sum(Table_TheoWin) >= sum(Table_Win) * @TableTierMultiplier then sum(Table_TheoWin) else sum(Table_Win) * @TableTierMultiplier end TableTierCreditsMonth1
            
      FROM CopyPlayerManagement..PlayerDay
      WHERE AccountingDate BETWEEN @BeginDateMonth1
                  AND @endDateMonth1
            AND Table_Games > 0
            and playerid = @PlayerID
      GROUP BY PlayerID)
                 
SELECT @TableTierCreditsMonth1 = TableTierCreditsMonth1 
from TempTableTierCreditsMonth1 

Open in new window

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Isn't that exactly the same SELECT I posted??
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
works fine if I use

SELECT @TableTierCreditsMonth1 = TableTierCreditsMonth1 from TempTableTierCreditsMonth1

But I wanted to capture another variable as well so I added

another select and it tells me the second one is invalid

can I only do 1?
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
it must be in the same select:
DECLARE @TableTierCreditsMonth1 as Decimal(18,2)
DECLARE @TableTierCreditsMonth2 as Decimal(18,2)
DECLARE @TableTierCreditsMonth3 as Decimal(18,2)


;WITH TempTableTierCreditsMonth1 (Playerid,TableTheoMonth1, TablewinMonth1, TableTierCreditsMonth1)
AS

(SELECT PlayerID
            ,sum(Table_TheoWin) AS TableTheoMonth1
            ,sum(Table_Win) AS TablewinMonth1
            ,case when sum(Table_TheoWin) >= sum(Table_Win) * @TableTierMultiplier then sum(Table_TheoWin) else sum(Table_Win) * @TableTierMultiplier end TableTierCreditsMonth1
            
      FROM CopyPlayerManagement..PlayerDay
      WHERE AccountingDate BETWEEN @BeginDateMonth1
                  AND @endDateMonth1
            AND Table_Games > 0
            and playerid = @PlayerID
      GROUP BY PlayerID)
                 
SELECT @TableTierCreditsMonth1 = TableTierCreditsMonth1 
 ,  @TableTierCreditsMonth2 = TableTierCreditsMonth2 
from TempTableTierCreditsMonth1 

Open in new window


again: the "CTE" is only part of the select, it's not "defined" to be reused by as many statements later as "needed".

Scott: I posted the full code just trying to make clear that one cannot reuse the CTE for several statements... the CTE expression is part of the statement ...

Author

Commented:
Thanks Guys!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial