September 25, 2003 at 6:27 am
Below works great except for the question about 5-6 lines down). The line "@Position = @Position 2" has "plus" sign in it but when I post it, it cuts it out.
I would appreciate any pointers - thank you very much --- if you have the time to look at it.
DECLARE @query varchar(2000), @InList varchar(100), @Position int, @roundX int
SET @InList = 'C6262,C6263'
SET @Position = 0
/* Why can't I use "SET @roundX = 3" and then call it in the WHERE clause of the query statement, in place of the number 3, I do this with the variable @InList */
SET @roundX = 3
WHILE @Position <> 1
BEGIN
SET @Position = CHARINDEX (',', @InList, @Position)
IF @Position <> 0
BEGIN
SET @InList = STUFF(@InList,@Position,1,''',''')
SET @Position = @Position + 2
END
ELSE
SET @Position = 1
END
SET @Query ='SELECT sim.simid, TeamNames.teamName, SimSuccess.TeamKey, SimTask.round, ROUND(SimSuccess.Stars_Sales / 100, 1) * 10 AS Stars_Sales,
ROUND(SimSuccess.Stars_Margins / 100, 1) * 10 AS Stars_Margins, ROUND(SimSuccess.Stars_Profits / 100, 1) * 10 AS Stars_Profits,
ROUND(SimSuccess.Stars_EmergencyLoans / 100, 1) * 10 AS Stars_EmergencyLoans, ROUND(SimSuccess.Stars_WorkingCapital / 100, 1)
* 10 AS Stars_WorkingCapital, ROUND(SimSuccess.Stars_Forecasting / 100, 1) * 10 AS Stars_Forecasting,
ROUND(SimSuccess.Stars_CustomerSatisfaction / 100, 1) * 10 AS Stars_CustomerSatisfaction, ROUND(SimSuccess.Stars_Productivity / 100, 1)
* 10 AS Stars_Productivity, ROUND(SimSuccess.Stars_FinancialStructure / 100, 1) * 10 AS Stars_FinancialStructure,
ROUND(SimSuccess.Stars_WealthCreation / 100, 1) * 10 AS Stars_WealthCreation, SimSuccess.Stars_Cumulative
FROM simsuccess inner join simtask on simsuccess.simtaskkey = simtask.simtaskkey inner join section on simtask.sectionkey = section.sectionkey inner join sim on section.sectionkey = sim.sectionkey inner join simteam on simsuccess.teamkey = simteam.teamkey inner join teamnames on simteam.teamnamekey = teamnames.teamnamekey
WHERE simtask.round =3 AND sim.simid IN (''' + @InList + ''')
ORDER BY sim.Simid, teamnames.teamname'
/* PRINT @Query */
EXEC (@Query)
September 25, 2003 at 8:41 am
And I imagine that you are saying ...
WHERE simtask.round = ' + CONVERT( VARCHAR(4), @roundX ) + ' AND....
Since you are building a string to execute, you need to convert it. @InList is already a string but you still catenate it in.
Guarddata-
September 25, 2003 at 10:33 am
I believe it has to do with SCOPE. Refer to the Books OnLine (BOL) for Variables and select the option for Transact-SQL, then search for scope.
BOL talks about how a GO will cause a variable to lose SCOPE. I believe the BEGIN and ENDs are doing the same thing.
-SQLBill
September 25, 2003 at 10:34 am
This is a closed topic thanks to guarddata - he hit it right on the head.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply