Reputation: 2636

SQL stored procedure passing parameter into "order by"

Using Microsoft SQL server manager 2008.

Making a stored procedure that will "eventually" select the top 10 on the Pareto list. But I also would like to run this again to find the bottom 10.

Now, instead of replicating the query all over again, I'm trying to see if there's a way to pass a parameter into the query that will change the order by from asc to desc.

Is there any way to do this that will save me from replicating code?

CREATE PROCEDURE [dbo].[TopVRM]
@orderby varchar(255)
AS
SELECT Peroid1.Pareto FROM dbo.Peroid1
GROUP by Pareto ORDER by Pareto @orderby

Upvotes: 7

Answers (5)

Cường Lương Quốc

Reputation: 1

CREATE PROCEDURE [dbo].[TopVRM]
@orderby varchar(255)
AS
SELECT Peroid1.Pareto FROM dbo.Peroid1
GROUP by Pareto 
ORDER by Pareto*CASE WHEN @sort = 'asc' THEN 1 ELSE -1 END

Upvotes: 0

Sekiro

Reputation: 28

This gives you more options

CREATE PROCEDURE [dbo].[TopVRM] @orderby varchar(255) = 'Pareto asc'
DECLARE @SendIt NVARCHAR(MAX)
AS
BEGIN
    SET @SendIt = 'SELECT Peroid1.Pareto FROM dbo.Peroid1
                   GROUP by Pareto ORDER by '+ @orderby
    EXEC sp_executesql @SendIt
END
GO
EXEC dbo.TopVRM 'Pareto DESC'
GO

Upvotes: 0

Raúl Diego

Reputation: 419

I know it's pretty old, but just wanted to share our solution here, hoping to help someone :)

After some performance tests for several candidate solutions (some of them posted in this thread), we realized you must be really careful with your implementation: your SP performance could be hugely impacted, specially when you combine it with pagination problem.

The best solution we found was to save raw results, ie. just applying filters, in a temporal table (#RawResult in the example), adding afterwards the ORDER BY and OFFSET clauses for pagination. Maybe it's not the prettiest solution (as you are force to copy & paste a clause twice for each column you want to sort), but we were unable to find other better in terms of performance.

Here it goes:

CREATE PROCEDURE [dbo].[MySP]
    -- Here goes your procedure arguments to filter results
    @Page INT = 1,                  -- Resulting page for pagination, starting in 1
    @Limit INT = 100,               -- Result page size
    @OrderBy NVARCHAR(MAX) = NULL,  -- OrderBy column
    @OrderByAsc BIT = 1             -- OrderBy direction (ASC/DESC)
AS

-- Here goes your SP logic (if any)

SELECT 
    * -- Here goes your resulting columns
INTO
    #RawResult
FROM 
    ...
-- Here goes your query data source([FROM], [WHERE], [GROUP BY], etc)
-- NO [ORDER BY] / [TOP] / [FETCH HERE]!!!!

--From here, ORDER BY columns must be copy&pasted twice: ASC and DESC orders for each colum
IF (@OrderByAsc = 1 AND @OrderBy = 'Column1')
    SELECT * FROM #RawResult ORDER BY Column1 ASC OFFSET @Limit * (@Page - 1) ROWS FETCH NEXT @Limit ROWS ONLY
ELSE
IF (@OrderByAsc = 0 AND @OrderBy = 'Column1')
    SELECT * FROM #RawResult ORDER BY Column1 DESC OFFSET @Limit * (@Page - 1) ROWS FETCH NEXT @Limit ROWS ONLY
ELSE
IF (@OrderByAsc = 1 AND @OrderBy = 'Column2')
    SELECT * FROM #RawResult ORDER BY Column2 ASC OFFSET @Limit * (@Page - 1) ROWS FETCH NEXT @Limit ROWS ONLY
ELSE
IF (@OrderByAsc = 0 AND @OrderBy = 'Column2')
    SELECT * FROM #RawResult ORDER BY Column2 DESC OFFSET @Limit * (@Page - 1) ROWS FETCH NEXT @Limit ROWS ONLY
ELSE
    ...
ELSE --Default order, first column ASC
    SELECT * FROM #RawResult ORDER BY 1 ASC OFFSET @Limit * (@Page - 1) ROWS FETCH NEXT @Limit ROWS ONLY

Upvotes: 2

Damien_The_Unbeliever

Reputation: 239636

Only by being slightly silly:

CREATE PROCEDURE [dbo].[TopVRM]
@orderby varchar(255)
AS
SELECT Peroid1.Pareto FROM dbo.Peroid1
GROUP by Pareto
ORDER by CASE WHEN @orderby='ASC' THEN Pareto END,
         CASE WHEN @orderby='DESC' THEN Pareto END DESC

You don't strictly need to put the second sort condition in a CASE expression at all(*), and if Pareto is numeric, you may decide to just do CASE WHEN @orderby='ASC' THEN 1 ELSE -1 END * Pareto

(*) The second sort condition only has an effect when the first sort condition considers two rows to be equal. This is either when both rows have the same Pareto value (so the reverse sort would also consider them equal), of because the first CASE expression is returning NULLs (so @orderby isn't 'ASC', so we want to perform the DESC sort.

You might also want to consider retrieving both result sets in one go, rather than doing two calls:

CREATE PROCEDURE [dbo].[TopVRM]
@orderby varchar(255)
AS

SELECT * FROM (
    SELECT
       *,
       ROW_NUMBER() OVER (ORDER BY Pareto) as rn1,
       ROW_NUMBER() OVER (ORDER BY Pareto DESC) as rn2
    FROM (
        SELECT Peroid1.Pareto
        FROM dbo.Peroid1
        GROUP by Pareto
    ) t
) t2
WHERE rn1 between 1 and 10 or rn2 between 1 and 10
ORDER BY rn1

This will give you the top 10 and the bottom 10, in order from top to bottom. But if there are less than 20 results in total, you won't get duplicates, unlike your current plan.

Upvotes: 15

Joe G Joseph

Reputation: 24046

try:

 CREATE PROCEDURE [dbo].[TopVRM]
(@orderby varchar(255)
AS
IF @orderby='asc'
SELECT Peroid1.Pareto FROM dbo.Peroid1
GROUP by Pareto ORDER by Pareto asc
ELSE
SELECT Peroid1.Pareto FROM dbo.Peroid1
GROUP by Pareto ORDER by Pareto desc

Upvotes: 1

SQL stored procedure passing parameter into &quot;order by&quot;

Answers (5)

Related Questions

SQL stored procedure passing parameter into "order by"