ORDER BY Stored Procedure

[Compkitty]

I'm trying to pass a param that is used to tell how to order by..

IE. 3 records
ITEM TYPE
Item1 P
Item2 W
Item3 S

Ok, the user puts in Items w/ P
so the sort order/OrderBy would put the P's first then the S's or the W's

Or, the user wants items of type W... So it would OrderBy W's then P's the S's...

Can this be done and if so; how... THANKS

 

[checkai]

dynamic sql....

set @sql = 'select field, field from tbl order by ' + @param

exec(@sql)

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[Compkitty]

Ok, can you give me a little better example or use this to put w/ my items? I'm still a little confused.. THANKS

 

[Compkitty]

The question I have is the @param in urs, How do I tell that what value to look at... say the column I want the @param value is Type... how do I do that

 

[hmckillop]

If you pass a param to tell which to order by you can use a case statement.
I would advise against dynamic sql as it can cause problems and is non-performant.

CREATE Proc usp_MyOrderProc
(@pi_OrderByCol varchar(35)
)
AS

SELECT * FROM MyTable
Order By 
CASE @pi_OrderByCol
	WHEN 'Field1' THEN Field1 
	WHEN 'Field2' THEN Field2 
	WHEN 'Field3' THEN Field3 
 ELSE Field1 
END

The only issue you may have with this is the types, all the types have to be the same if they arent just use a convert statement in the CASE i.e.
WHEN 'Field1' THEN convert(varchar, Field1) etc.



"I'm living so far beyond my income that we may almost be said to be living apart

 

[Catadmin]

You can create a stored procedure using checkai's code:

Create Procedure up_sortingorder
as
Declare @param char(1)
set @sql = 'select field, field from tbl order by ' + @param
Exec (@sql)

Then execute it as:

Exec up_sortingorder P

whenever you need the procedure. You'll need to clean up the Select statement to look the way you want it to, though.





Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[Compkitty]

Ok, But @param is not connected to a field value.. Ie. 3 cols - Item, date, type

Create Procedure up_sortingorder
as
Declare @param char(1)
set @sql = 'select Item, date, type  from tbl order by ' + @param
Exec (@sql)

How does it know that I want to order by type w/ value "p"

 

[Compkitty]

Ok below is what I have

CREATE PROCEDURE Stp_TestIdx
(

@OrderByClause varchar (2)
)

 AS

DECLARE @SQLStatement varchar (255)

SELECT @SQLStatement = 'SELECT ID, SelectType FROM  tblManagerIdx  ORDER BY'  +  ' " ' + @OrderByClause + ' " ' 

EXEC(@SQLStatement)
GO

but I'm still not getting the right result.. the SelectType is either an A, B, or C.. It will run, however, if I change letters it's still sorting the same. Also, I need to add in the (WHERE) for the SqlStatement... can I do this..
I guess I'm just not understanding how the OrderByClause knows which column value to use...

 

[Catadmin]

CompKitty,

I didn't catch on that you wanted specific ordering of the columns. My bad. In that case, you need to follow hmckillop's CASE statement suggestion. But you'll have to add your variable declaration to his code so you can tell it what type to order it by.

See if that works for you.



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[philhege]

The idea here is to prioritize one type of field value. A straight ORDER BY won't do that. You need to add a field to the return that flags the field value you want prioritized, and then order by that flag field.

So,

--@orderbyclause is the selecttype value you want prioritized

CREATE PROCEDURE Stp_TestIdx
(

@OrderByClause varchar (2)
)

AS

DECLARE @SQLStatement varchar (255)

SELECT @SQLStatement = 'SELECT ID, SelectType, CASE WHEN SelectType = '''+@OrderByClause+''' THEN 0 else 1 END
AS orderfield FROM tblManagerIdx ORDER BY orderfield, selectType'

EXEC(@SQLStatement)
GO

HTH,



Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[Compkitty]

Specific ordering of values in the columns.. passing P to the stored procedure will order the col1 by Ps then rest to follow...

 

[Compkitty]

Ok here is what I have now.. (going w/ philhege)

CREATE PROCEDURE Stp_TestIdx
(

@OrderByClause varchar (2)
)

 AS

DECLARE @SQLStatement varchar (255)

SELECT @SQLStatement = 'SELECT   ID, SelectType, CASE WHEN SelectType = ' +  " ' + @OrderByClause + ' " +  '   THEN 0 else 1 END AS Orderfield   FROM  ManagerId' +
		   ' ORDER BY Orderfield, SelectType ' 


EXEC(@SQLStatement)
GO

it's running however, if I put "P" in, the value of Orderfield is 1 and it's sorted by P.. Same if I put in S... it's like it's not getting the value some how..

 

[philhege]

The double quotes should be escaped single quotes.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[SQLBill]

To clarify what Phil said...

Everywhere you have " (one double quote) in your script above, you need to change to '' (two single quotes).

In SQL Server " <> ''

-SQLBill

Posting advice: FAQ481-4875