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.
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
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..Play
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
(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
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..Play
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
Asked:
2
2-
2
2 Solutions
I agree with above comment, the SET alone won't work, as it "forgot" the CTE already.
this will work better:
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
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?
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?
it must be in the same select:
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 ...
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
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 ...
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 Already a member? Login.
Featured Post
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.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
SELECT @TableTierCreditsMonth1 = TableTierCreditsMonth1 from TempTableTierCreditsMonth1