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.
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You have to have a SELECT statement after a CTE:
SELECT @TableTierCreditsMonth1 = TableTierCreditsMonth1 from TempTableTierCreditsMonth1
SELECT @TableTierCreditsMonth1 = TableTierCreditsMonth1 from TempTableTierCreditsMonth1
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 ...
Premium Content
You need an Expert Office subscription to comment.Start Free Trial