How to pass a column name and sort direction

[CzechNET]

I'm trying to create a stored procedure into which I'd like to pass the column name and direction by which to sort the result. For example I'd like to pass "fldNAME DESC" as the @ORDER. Is this possible ? Thanks.

CREATE PROCEDURE sp_MY_POROC
@ID as INT
@ORDER AS Something
AS
SELECT fldNAME FROM tblTABLE WHERE ID = @ID ORDER BY
GO

 

[johnherman]

build your string in a working variable and issue
EXEC sp_executesql @executestring

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[donutman]

I like to avoid the use of dynamic SQL if possible. If the choice of columns is limited to the same datatype then I'd definately use a CASE statement for the ORDER BY. Even if it isn't you can convert them to the same type (for most datatypes anyway).

[Blue]ORDER[/Blue] [Blue]BY[/Blue] [Blue]CASE[/Blue] @SomeThing [Blue]WHEN[/Blue] [red]'ColA'[/red] [Blue]THEN[/Blue] ColA[Gray],[/Gray]
            [Blue]WHEN[/Blue] [red]'ColB'[/red] [Blue]THEN[/Blue] ColB[Gray],[/Gray] etc [Blue]END[/Blue]

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Mr3Putt]

I'm going to post a sample StoredProc... it sucks to write these things, and this is (as absolutely far as I know) the only way to make variable sortfield + direction.

One of the tips is, you MUST group fields of similar types (date, string, numbers) into the same set of CASE statements... and you CAN NOT combine ordinals from a CASE statement with ORDINALs outside the case statement. Which seriously blows. So, as you have no-doubt found, you can't pass an ordinal and say ORDER BY @iSortCol, 2

The following code seems very long (and I've removed almost all of the actual fields, which you can tell since my @iSortCol variable accounts for numbers as high as 19, but I'm only selecting 2 columns. Hopefully this will give you a hint on the process.

An even better result would be for SQLSister to look at the following block of goo and help simplify things for me, too. ;-)

/*****************************
NAME:        wsp_getSomeStuffAndSortIt
CREATED:     11/01/2003 Mr3Putt
REFERENCE:   None
PURPOSE:     A public sample of my ineptitude
REVISIONS:   Mr3Putt 11.2004 deleted almost everything
*****************************/

ALTER Procedure wsp_getBLPTrFd_Trades
(
 @iSortCol int = 0,
 @sSortDir varchar(4) = 'DESC',
 @dTradeDt smalldatetime
)
AS
SET NOCOUNT OFF
IF @sSortDir = 'ASC' -- ASCENDING SORT ORDER
  BEGIN
    SELECT t.nPrice, p.sTransDescr
    FROM dbo.TableOfStuff t 
    WHERE (@dTradeDt = t.TradeDate)
    ORDER BY 
     CASE @iSortCol -- SORT NUMERIC COLUMNS
       WHEN 0 THEN t.iTrFdID
       WHEN 1 THEN t.iTSN
       WHEN 2 THEN t.iStartTrnNbr
       WHEN 7 THEN t.iTrRecType
       WHEN 8 THEN t.nTradeAmt
       WHEN 13 THEN t.nPrice
     END ASC,
     CASE @iSortCol -- SORT TEXT COLUMNS
       WHEN 3 THEN t.sTraderName
       WHEN 4 THEN t.sSecID
       WHEN 17 THEN p.sTransDescr
       WHEN 19 THEN c.sOurTrnCode
     END ASC,
     CASE @iSortCol -- SORT DATE COLUMNS
       WHEN 9 THEN t.dTradeDt
       WHEN 12 THEN t.dMaturity
       WHEN 15 THEN t.dLastUpdDt
     END ASC,
 1 DESC  [COLOR=green]-- default sort by first column[/color]
  END
ELSE
  BEGIN
    SELECT t.nPrice, p.sTransDescr  -- DESCENDING ORDER (default)
    FROM dbo.TableOfStuff t 
    WHERE (@dTradeDt = t.TradeDate)
    ORDER BY 
     CASE @iSortCol -- SORT NUMERIC COLUMNS
       WHEN 0 THEN t.iTrFdID
       WHEN 1 THEN t.iTSN
       WHEN 2 THEN t.iStartTrnNbr
       WHEN 7 THEN t.iTrRecType
       WHEN 8 THEN t.nTradeAmt
     END DESC,
     CASE @iSortCol -- SORT TEXT COLUMNS
       WHEN 3 THEN t.sTraderName
       WHEN 4 THEN t.sSecID
       WHEN 5 THEN t.sBuySellFlag
       WHEN 19 THEN c.sOurTrnCode
     END DESC,
     CASE @iSortCol -- SORT DATE COLUMNS
       WHEN 9 THEN t.dTradeDt
       WHEN 10 THEN t.dSettleDt
       WHEN 12 THEN t.dMaturity
       WHEN 15 THEN t.dLastUpdDt
     END DESC,
 1 DESC
  END
SET NOCOUNT ON

I hope that this is helpful... good luck on your project.

[red]Note:[/red] [gray]The above comments are the opinionated ravings of Mr3Putt. As such, Mr3Putt accepts no responsibility for damages, real or contrived, resulting from acceptance of his opinions as fact.[/gray]

 

[CzechNET]

Thanks guys, right after my head explodes I'll try to implement it Close to first time using Store Procs, so far it's a love hate relationship ...

 

[donutman]

If you promise to love Stored Procs and hate Dynamic SQL, Cursors, Triggers and User Defined Functions, then you "can be my wing-man any day."
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[CzechNET]

I do love it so far Saves on code I have to write, separates the application logic give me more power it's faster and more organized .... no reason not to use it

One question though, how difficult is it to port MSSQL SP's to say Oracle SP's ?

 

[donutman]

Oracle? What's that, part of the Matrix?

 

[SQLSister]

Case is an ansi SQL function, but I'm not sure if Oracle supports it. The book I have (which is a couple of versions of Oracle out of date) says it does not, but that the DECODE function performs essentially the same thing.

Questions about posting. See faq183-874

 

[MacLeod72]

I know that Oracle 9i supports the CASE functionality, and I believe that 8i does as well. DECODE is still supported in all recent versions of Oracle.

--Rob