Is the following possible within a Stored Procedure
-
Hi there.
I have a stored procedure that calls data from one of our tables based on a certain parameter (yearmonth) and then creates and inserts that data into a temporary table using row_number() to order the data by amount, with the highest amount being the first row in the table.
Then from that table there is a select statement that requires a parameter based on user input (their username/alias) and returns a result set.
Now here is where the tricky part comes in:
I want to be able to pull the users data based on their query (username/alias) as well as 3 rows before and 3 rows after.
To make it trickier, if the user query returns the first row, then I want it only to show 3 rows after as there are no rows before.
Is that possible using functions or Transact-SQL or would I have to code those requirements using my ASP function that shows the users results...
Thanks.
Byron
-
Byron
Please will you give us the CREATE TABLE statement for your temporary table.
Also, please have a read of this - it'll help others to help you as quickly as they can.
http://www.aspfaq.com/etiquette.asp?id=5006
Thanks
John
-
Hi John.
I was able to come up with something that works exactly the way I need, but I think that it may be very server intensive if many people are doing the same queries together.
If a person is in first place it only shows 3 results that are below them, as their is nothing above, but if the person is in say 10th, it will return 3 people before (7,8,9) and 3 people after (11,12,13).
It also adds that the person that is doing the query gets marked as the person who does the query, for output purposes.
Can I maybe lighten the code a little, or this how its meant to be done. Thanks
Here is the stored procedure
======================================================
alter proc usp_MPVRankByAlias
@CurrentMonth int,
@PlayerAlias varchar(50)
as
set nocount on
-- Create Temp Table to Store Data based on the first param (@CurrentMonth)
create table #tblMPVAliasByRank(
Rank int,
Alias varchar(50),
Country varchar(25),
Yearmonth int,
PrizeAmount decimal(10,2)
)
-- Create Temp Table to store results based on T-SQL and second param (@PlayerAlias)
create table #RankResults(
Rank int,
Alias varchar(50),
Country varchar(25),
Yearmonth int,
PrizeAmount decimal(10,2),
Requester varchar(50)
)
-- Insert data ordered by PrizeAmount for the current month
insert into #tblMPVAliasByRank (Rank, Alias, Country, Yearmonth, PrizeAmount)
select ROW_NUMBER() over (order by PrizeAmount desc) as Rank, Alias, Country, Yearmonth, PrizeAmount
from tblMPVLeaderBoard where Yearmonth = @CurrentMonth
-- Insert results from the person who requested their ranking and mark as requester
insert into #RankResults (Rank, Alias, Country, Yearmonth, PrizeAmount)
select * from #tblMPVAliasByRank where Alias = @PlayerAlias
update #RankResults set Requester = @PlayerAlias where Alias = @PlayerAlias
-- Start 3 Prior Rows
insert into #RankResults (Rank, Alias, Country, Yearmonth, PrizeAmount)
select * from #tblMPVAliasByRank where Rank =
(Select Rank from #tblMPVAliasByRank where Alias = @PlayerAlias)-1
insert into #RankResults (Rank, Alias, Country, Yearmonth, PrizeAmount)
select * from #tblMPVAliasByRank where Rank =
(Select Rank from #tblMPVAliasByRank where Alias = @PlayerAlias)-2
insert into #RankResults (Rank, Alias, Country, Yearmonth, PrizeAmount)
select * from #tblMPVAliasByRank where Rank =
(Select Rank from #tblMPVAliasByRank where Alias = @PlayerAlias)-3
-- Start 3 After Rows
insert into #RankResults (Rank, Alias, Country, Yearmonth, PrizeAmount)
select * from #tblMPVAliasByRank where Rank =
(Select Rank from #tblMPVAliasByRank where Alias = @PlayerAlias)+1
insert into #RankResults (Rank, Alias, Country, Yearmonth, PrizeAmount)
select * from #tblMPVAliasByRank where Rank =
(Select Rank from #tblMPVAliasByRank where Alias = @PlayerAlias)+2
insert into #RankResults (Rank, Alias, Country, Yearmonth, PrizeAmount)
select * from #tblMPVAliasByRank where Rank =
(Select Rank from #tblMPVAliasByRank where Alias = @PlayerAlias)+3
select * from #RankResults order by Rank
-
tblMPVLeaderBoard - is the main table that the data is taken from
and the create statement for that is
create table tblMPVLeaderBoard(
LeaderBoardID int,
Yearmonth varchar(50),
Alias nvarchar(40),
Country varchar(50) int,
PrizeAmount decimal(10,2)
)
and the insert statement for 10 results
INSERT INTO [tblMPVLeaderboard] ([LeaderBoardID],[Yearmonth],[Alias],[Country],[PrizeAmount])VALUES(1,200705,'8ell','USA',1.50)
INSERT INTO [tblMPVLeaderboard] ([LeaderBoardID],[Yearmonth],[Alias],[Country],[PrizeAmount])VALUES(2,200705,'aaronp85','USA',34.75)
INSERT INTO [tblMPVLeaderboard] ([LeaderBoardID],[Yearmonth],[Alias],[Country],[PrizeAmount])VALUES(3,200705,'abbydabby','Canada',2.00)
INSERT INTO [tblMPVLeaderboard] ([LeaderBoardID],[Yearmonth],[Alias],[Country],[PrizeAmount])VALUES(4,200705,'AsianQueen4o8','USA',10.00)
INSERT INTO [tblMPVLeaderboard] ([LeaderBoardID],[Yearmonth],[Alias],[Country],[PrizeAmount])VALUES(5,200705,'auntyflash','USA',10.00)
INSERT INTO [tblMPVLeaderboard] ([LeaderBoardID],[Yearmonth],[Alias],[Country],[PrizeAmount])VALUES(6,200705,'Babonator','USA',26.25)
INSERT INTO [tblMPVLeaderboard] ([LeaderBoardID],[Yearmonth],[Alias],[Country],[PrizeAmount])VALUES(7,200705,'bbjoe','United Kingdom',3.00)
INSERT INTO [tblMPVLeaderboard] ([LeaderBoardID],[Yearmonth],[Alias],[Country],[PrizeAmount])VALUES(8,200705,'blaire20037','Canada',41.25)
INSERT INTO [tblMPVLeaderboard] ([LeaderBoardID],[Yearmonth],[Alias],[Country],[PrizeAmount])VALUES(9,200705,'blutopaz','USA',85.00)
INSERT INTO [tblMPVLeaderboard] ([LeaderBoardID],[Yearmonth],[Alias],[Country],[PrizeAmount])VALUES(10,200705,'Cannibal_Holocaust','USA',18.00)
-
So this is on SQL2005 ?
Try this new CTE :
-- replaced your persistent table to #temptb just for testing
create
table #tblMPVLeaderBoard(
LeaderBoardID int,
Yearmonth INT , --varchar(50),
Alias nvarchar(40),
Country VARCHAR(50) ,
PrizeAmount decimal(10,2)
)
--and the insert statement for 10 results
SET
NOCOUNT on
INSERT
INTO #tblMPVLeaderBoard ([LeaderBoardID],[Yearmonth],[Alias],[Country],[PrizeAmount])VALUES(1,200705,'8ell','USA',1.50)
INSERT
INTO #tblMPVLeaderBoard ([LeaderBoardID],[Yearmonth],[Alias],[Country],[PrizeAmount])VALUES(2,200705,'aaronp85','USA',34.75)
INSERT
INTO #tblMPVLeaderBoard ([LeaderBoardID],[Yearmonth],[Alias],[Country],[PrizeAmount])VALUES(3,200705,'abbydabby','Canada',2.00)
INSERT
INTO #tblMPVLeaderBoard ([LeaderBoardID],[Yearmonth],[Alias],[Country],[PrizeAmount])VALUES(4,200705,'AsianQueen4o8','USA',10.00)
INSERT
INTO #tblMPVLeaderBoard ([LeaderBoardID],[Yearmonth],[Alias],[Country],[PrizeAmount])VALUES(5,200705,'auntyflash','USA',10.00)
INSERT
INTO #tblMPVLeaderBoard ([LeaderBoardID],[Yearmonth],[Alias],[Country],[PrizeAmount])VALUES(6,200705,'Babonator','USA',26.25)
INSERT
INTO #tblMPVLeaderBoard ([LeaderBoardID],[Yearmonth],[Alias],[Country],[PrizeAmount])VALUES(7,200705,'bbjoe','United Kingdom',3.00)
INSERT
INTO #tblMPVLeaderBoard ([LeaderBoardID],[Yearmonth],[Alias],[Country],[PrizeAmount])VALUES(8,200705,'blaire20037','Canada',41.25)
INSERT
INTO #tblMPVLeaderBoard ([LeaderBoardID],[Yearmonth],[Alias],[Country],[PrizeAmount])VALUES(9,200705,'blutopaz','USA',85.00)
INSERT
INTO #tblMPVLeaderBoard ([LeaderBoardID],[Yearmonth],[Alias],[Country],[PrizeAmount])VALUES(10,200705,'Cannibal_Holocaust','USA',18.00)
SET
NOCOUNT OFF
DECLARE
@CurrentMonth INT
DECLARE
@PlayerAlias NVARCHAR(40)
SET
@CurrentMonth = 200705
SET
@PlayerAlias = N'AsianQueen4o8'
;
with cteMPVAliasByRank (Ranking, Alias, Country, Yearmonth, PrizeAmount)
as (select ROW_NUMBER() over (order by PrizeAmount desc) , Alias, Country, Yearmonth, PrizeAmount
from #tblMPVLeaderBoard where Yearmonth = @CurrentMonth )
SELECT M.*
FROM cteMPVAliasByRank M
INNER JOIN cteMPVAliasByRank PA
ON PA.Alias = @PlayerAlias
AND M.Ranking BETWEEN PA.Ranking - 3 AND PA.Ranking + 3 ;
ps. update the query innerjoin part in stead of the double where-clause
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data and code to get the best help- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
excuse my ignorance... I am new to SQL Server.
Yes this is SQL Server 2005.
Can CTE's be used within a Stored Procedure? It works fine within SQL Server Management Studio if I do a query on your code directly, just trying something based on way you have given me. Thanks!
-
Can CTE's be used within a Stored Procedure?
Yes they can !
CTE's are new with Tsql in SQL2005. Search BOL or SSC
for more info.Just take care to start your cte definition with a ;
The with statement needs to be preceded with a semicolon !
(in fact the preceding statement needs to be ending with one
)but it doesn't hurt to have two, so start your with-clause with a semicolon.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data and code to get the best help- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks for the heads up on CTE... Much easier than my previous query and much faster when i tested it on the real table.
Here is the new stored procedure which is cut down from 65 lines to 16.
alter proc usp_MPVRankByAlias
@CurrentMonth int,
@PlayerAlias varchar(50)
as
with cteAliasByRank (Rank, Alias, Country, Yearmonth, PrizeAmount) as
(
select ROW_NUMBER() over (order by PrizeAmount desc) as
Rank, Alias, Country, Yearmonth, PrizeAmount
from tblMPVLeaderBoard where Yearmonth = @CurrentMonth
)
select * from cteAliasByRank where
Rank >= (select min(Rank) - 3 from cteAliasByRank where alias = @PlayerAlias)
and
Rank <= (select min(Rank) + 3 from cteAliasByRank where alias = @PlayerAlias)
ALZDBA you are a legend. Thanks. I knew there was a quicker way just not used to all these transactional functions that are built into SQL Server 2005.
-
Because of the contraints you have in the CTE, you might not even need MIN in the sub-selects.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
btw the most common use of CTE's is as a replacement for nested table expressions.
In many cases in join operations, CTE's will outperform repeated nested table expressions.
Check BOL or SSC
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data and code to get the best help- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
indeed, that's a little confusing flaw in my code part.
As you can see my first code post, I'd updated it before you replied.
Just for completeness this should be the query :
;with cteMPVAliasByRank (Ranking, Alias, Country, Yearmonth, PrizeAmount)
as (select ROW_NUMBER() over (order by PrizeAmount desc) , Alias, Country, Yearmonth, PrizeAmount
from #tblMPVLeaderBoard where Yearmonth = @CurrentMonth )
SELECT M.*
FROM cteMPVAliasByRank M
INNER JOIN cteMPVAliasByRank PA
ON PA.Alias = @PlayerAlias
AND M.Ranking BETWEEN PA.Ranking - 3 AND PA.Ranking + 3 ;
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data and code to get the best help- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
@ Jeff: Thanks, I have just tested it and it works without the min statement.
@ ALZDBA: I will definitely look into BOL and SSC, but I have one last thing before I go.
In my old SP I had an extra column called Requester, so that I could map who had done the request (for presentation) but if I try and add it to the CTE statement I get an error because we are populating the CTE based on a select statement and there are less columns in that select statements Table.
Is there a way to add that column and add the contents within that same CTE?
-
No problem, just add the highlighted
;with cteMPVAliasByRank (Ranking, Alias, Country, Yearmonth, PrizeAmount)
as (select ROW_NUMBER() over (order by PrizeAmount desc) , Alias, Country, Yearmonth, PrizeAmount
from #tblMPVLeaderBoard where Yearmonth = @CurrentMonth )
SELECT M.*, cast(@PlayerAlias as varchar(50) ) as Requester
FROM cteMPVAliasByRank M
INNER JOIN cteMPVAliasByRank PA
ON PA.Alias = @PlayerAlias
AND M.Ranking BETWEEN PA.Ranking - 3 AND PA.Ranking + 3 ;
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data and code to get the best help- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Heh... sorry... you post faster than I do
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I try to write slow, for those who cannot read fast
Seriously, today has been one of the few days in months that I've been able to perform some research on the web, also giving me time to reply frequent.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data and code to get the best help- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply