Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.
Course Of The Month
11 days, 9 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Set variable error
Posted on 2012-12-28
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2
2-
2
6 Comments
Active today
You have to have a SELECT statement after a CTE:
SELECT @TableTierCreditsMonth1 = TableTierCreditsMonth1 from TempTableTierCreditsMonth1
SELECT @TableTierCreditsMonth1 = TableTierCreditsMonth1 from TempTableTierCreditsMonth1
Active 6 days ago
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
Active today
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?
Active 6 days ago
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 ...
Featured Post
Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article explains how to reset the password of the sa account on a Microsoft SQL Server. The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes. We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month11 days, 9 hours left to enroll
623 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.