Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

Set variable error

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
0
johnnyg123
Asked:
johnnyg123
  • 2
  • 2
  • 2
2 Solutions
 
Scott PletcherSenior DBACommented:
You have to have a SELECT statement after a CTE:

SELECT @TableTierCreditsMonth1 = TableTierCreditsMonth1 from TempTableTierCreditsMonth1
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
Scott PletcherSenior DBACommented:
Isn't that exactly the same SELECT I posted??
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
johnnyg123Author 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?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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 ...
0
 
johnnyg123Author Commented:
Thanks Guys!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now