June 12, 2012 at 9:58 pm
In this example, I'm selecting the TOP 1 student who has a tag = 0 and in grade = 7 using the age DESC as sort.
Question: Is there way to add in the WHERE statement something that would accomplish this?
IF @AppleFlag = 1 THEN consider all students who have apple count > 0
IF @PearFlag = 1 THEN consider all students who have pear count > 0
IF @BananaFlag = 1 THEN consider all students who have banana count > 0
None or all of these flags can be valued 1.
IF OBJECT_ID('tempdb..#Worked') > 0 DROP TABLE #Worked
CREATE TABLE #Worked
(Student varchar(20), Grade int, Apple int, Pear int, Banana int, Age int, Tag int)
INSERT INTO #Worked
(Student, Grade, Apple, Pear, Banana, Age, Tag)
SELECT 'Joe', 8, 2, 2, 2, 21, 0 UNION ALL
SELECT 'Sue', 8, 3, 1, 2, 20, 0 UNION ALL
SELECT 'Jan', 8, 4, 0, 1, 19, 0 UNION ALL
SELECT 'Ali', 7, 0, 0, 1, 18, 0 UNION ALL
SELECT 'Moe', 7, 0, 2, 0, 17, 0 UNION ALL
SELECT 'Sid', 7, 0, 2, 0, 16, 0
DECLARE @UseIt varchar(20)
DECLARE @Grade int
DECLARE @AppleFlag int
DECLARE @Cint
DECLARE @BananaFlag int
SET @Grade = 8
SET @AppleFlag = 0
SET @PearFlag = 0
SET @BananaFlag = 0
UPDATE #Worked SET
Tag = 1,
@UseIt = A.Student
FROM #Worked A (UPDLOCK)
INNER JOIN
(
SELECT TOP 1 A.Student
FROM #Worked A (UPDLOCK)
WHERE Tag = 0
AND Grade = @Grade
ORDER BY Age DESC
) Z
ON A.Student = Z.Student
June 13, 2012 at 1:15 am
You could use dynamic SQL
DECLARE @UseIt varchar(20)
DECLARE @Grade int
DECLARE @AppleFlag int
DECLARE @PearFlag int
DECLARE @BananaFlag int
DECLARE @SQL NVARCHAR(MAX)
SET @Grade = 8
SET @AppleFlag = 0
SET @PearFlag = 0
SET @BananaFlag = 0
SET @SQL = 'UPDATE #Worked SET Tag = 1, @UseIt = A.Student FROM #Worked A INNER JOIN (SELECT TOP 1 B.Student FROM #Worked B) WHERE Tag = 0 AND Grade = @Grade'
IF @AppleFlag = 1
SET @SQL = @SQL + ' AND Apple > 0'
IF @PearFlag = 1
SET @SQL = @SQL + ' AND Pear > 0'
IF @BananaFlag = 1
SET @SQL = @SQL + ' AND Banana > 0'
SET @SQL = @SQL + ' ORDER BY Age DESC) Z ON A.Student = Z.Student'
EXEC sp_executesql @SQL, N'@Grade int', @Grade = @Grade
June 13, 2012 at 4:49 am
Initial post deleted.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 13, 2012 at 5:40 am
This might work but it may end up being slower without some kind of indexing:
;WITH CTE AS (
SELECT Student, Grade, Apple, Pear, Banana, Age, Tag
,n=ROW_NUMBER() OVER (
PARTITION BY Grade, Tag
ORDER BY CASE WHEN @AppleFlag*Apple > 0 THEN 0 ELSE 1 END
,CASE WHEN @PearFlag*Pear > 0 THEN 0 ELSE 1 END
,CASE WHEN @BananaFlag*Banana > 0 THEN 0 ELSE 1 END
,Age DESC)
FROM #Worked
)
--SELECT * FROM CTE
UPDATE A
SET Tag = 1, @UseIt = A.Student
FROM CTE A
WHERE Tag = 0 AND Grade = @Grade and n = 1
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 13, 2012 at 7:22 am
Anthony thanks. The concept of this looks like something I can understand.
I copied and pasted this exactly as written but I get two errors:
must declare scalar variable "@UseIt"
incorrect syntax near the keyword WHERE
appreciate it!
June 13, 2012 at 7:31 am
Dwain, looks good, thanks
June 13, 2012 at 5:07 pm
This is Andrew's solution which is very interesting. I get two errors when I excute this:
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@UseIt".
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WHERE'.
Any ideas on the issue? Thanks!
IF OBJECT_ID('tempdb..#Worked') > 0 DROP TABLE #Worked
CREATE TABLE #Worked
(Student varchar(20), Grade int, Apple int, Pear int, Banana int, Age int, Tag int)
INSERT INTO #Worked
(Student, Grade, Apple, Pear, Banana, Age, Tag)
SELECT 'Joe', 8, 2, 2, 2, 21, 0 UNION ALL
SELECT 'Sue', 8, 3, 1, 2, 20, 0 UNION ALL
SELECT 'Jan', 8, 4, 0, 1, 19, 0 UNION ALL
SELECT 'Ali', 7, 0, 0, 1, 18, 0 UNION ALL
SELECT 'Moe', 7, 0, 2, 0, 17, 0 UNION ALL
SELECT 'Sid', 7, 0, 2, 0, 16, 0
DECLARE @UseIt varchar(20)
DECLARE @Grade int
DECLARE @AppleFlag int
DECLARE @PearFlag int
DECLARE @BananaFlag int
DECLARE @SQL NVARCHAR(MAX)
SET @Grade = 8
SET @AppleFlag = 0
SET @PearFlag = 0
SET @BananaFlag = 0
SET @SQL = 'UPDATE #Worked SET Tag = 1, @UseIt = A.Student FROM #Worked A INNER JOIN (SELECT TOP 1 B.Student FROM #Worked B) WHERE Tag = 0 AND Grade = @Grade'
IF @AppleFlag = 1
SET @SQL = @SQL + ' AND Apple > 0'
IF @PearFlag = 1
SET @SQL = @SQL + ' AND Pear > 0'
IF @BananaFlag = 1
SET @SQL = @SQL + ' AND Banana > 0'
SET @SQL = @SQL + ' ORDER BY Age DESC) Z ON A.Student = Z.Student'
EXEC sp_executesql @SQL, N'@Grade int', @Grade = @Grade
June 13, 2012 at 5:27 pm
SELECT TOP 1 B.Student FROM #Worked B) WHERE Tag = 0
Need to get rid of the parenthesis?
Having problems after this executes, would like to be able to:
SELECT @UseIt
June 14, 2012 at 1:26 am
Have you checked the query and the syntax for sp_executesql?
June 14, 2012 at 12:44 pm
UPDATE #Worked
SET
Tag = 1,
@UseIt = A.Student
FROM #Worked A (UPDLOCK)
INNER JOIN
(
SELECT TOP 1
A.Student
FROM #Worked A (UPDLOCK)
WHERE
Tag = 0
AND Grade = @Grade
AND (@AppleFlag = 0 OR Apple > 0)
AND (@PearFlag = 0 OR Pear > 0)
AND (@BananaFlag = 0 OR Banana > 0)
ORDER BY
Age DESC
) Z
ON A.Student = Z.Student
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 14, 2012 at 1:45 pm
This is as simple as a question as I can come up with for this.
Can I get the DECLARE out of the @SQL area?
How can I get SELECT @UseIt where it is located in the procedure?
Thanks, I'm lost on this!
IF OBJECT_ID('tempdb..#Sample') > 0 DROP TABLE #Sample
CREATE TABLE #Sample
(Student varchar(20), Grade int, Selected int)
INSERT INTO #Sample
( Student, Grade, Selected)
SELECT 'Joe', 9, 0 UNION ALL
SELECT 'Sue', 8, 0 UNION ALL
SELECT 'Jan', 7, 0 UNION ALL
SELECT 'Ali', 6, 0 UNION ALL
SELECT 'Moe', 5, 0 UNION ALL
SELECT 'Sid', 4, 0
DECLARE @SQL NVARCHAR(MAX)
DECLARE @UseIt varchar(20)
DECLARE @Grade int
SET @Grade = 8
SET @SQL =
'DECLARE @UseIt varchar(20)
UPDATE #Sample
SET Selected = 1,
@UseIt = A.Student
FROM #Sample A
INNER JOIN
(SELECT TOP 1 B.Student
FROM #Sample B
WHERE Selected = 0
AND Grade = @Grade
) Z ON A.Student = Z.Student'
EXEC sp_executesql @SQL, N'@Grade int', @Grade = @Grade
select * from #Sample where Selected = 1
SELECT @UseIt
June 14, 2012 at 6:22 pm
anthony.green (6/14/2012)
Have you checked the query and the syntax for sp_executesql?
I would use sp_executesql too in the real world. Too lazy to post solutions to the forum in that syntax though.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 14, 2012 at 8:36 pm
OK, figured it out ... needed OUTPUT
IF OBJECT_ID('tempdb..#Sample') > 0 DROP TABLE #Sample
CREATE TABLE #Sample
(Student varchar(20), Grade int, Selected int)
INSERT INTO #Sample
( Student, Grade, Selected)
SELECT 'Joe', 9, 0 UNION ALL
SELECT 'Sue', 8, 0 UNION ALL
SELECT 'Jan', 7, 0 UNION ALL
SELECT 'Ali', 6, 0 UNION ALL
SELECT 'Moe', 5, 0 UNION ALL
SELECT 'Sid', 4, 0
DECLARE @SQL NVARCHAR(MAX)
DECLARE @SqlParam NVARCHAR(MAX)
DECLARE @UseItId NVARCHAR(25)
DECLARE @Grade int
SET @Grade = 8
SET @SQL =
'UPDATE #Sample
SET Selected = 1,
@UseItOUT = A.Student
FROM #Sample A
INNER JOIN
(SELECT TOP 1 B.Student
FROM #Sample B
WHERE Selected = 0
AND Grade = @Grade
) Z ON A.Student = Z.Student'
SET @SqlParam = N'@Grade int, @UseItOUT NVARCHAR(25) OUTPUT';
EXEC sp_executesql @SQL, @SqlParam, @UseItOUT = @UseItId OUTPUT, @Grade = @Grade
SELECT @UseItId
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply