Order By using a Variable

Question

Post reply

Order By using a Variable

JuanBob

SSCertifiable

Points: 5059
More actions
October 12, 2005 at 12:11 pm

#89680

I have created a Stored Procedure that I would like to have a dynamic sort. Here's an example:
create procedure [dbo].[sp_query] (
@sort varchar(20)
)
AS
Select * From Table
Order by @sort

Upon validating the query, it pops an error saying that that sort must be against a Column name. How can I do this?
Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you'll either need to use dynamic SQL, which should generally be avoided, or use a if-then-else statement like so:

dynamic SQL:

@sql='SELECT * FROM TABLENAME ORDER BY ' + @sort

EXECUTE(@SQL)

Logical parameters.

create procedure [dbo].[sp_query] (@sort varchar(20) )

AS

if @sort='FIRSTNAME'

Select * From Table order by FIRSTNAME

elseif @sort='PHONENBR'

Select * From Table order by 'PHONENBR'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ramses2nd Hall of Fame Points: 3720 More actions · Answer 2

The way I got around this was by using a case by statement in the order by clause. Here is an example:

CREATE PROCEDURE Return_EmpInfo ( @OrderColumn varchar(100) ) AS

SELECT EmpID, FName, LName, Title

FROM Employee

ORDER BY

CASE WHEN @OrderColumn = 'LName' THEN LName

WHEN @OrderColumn = 'FName' THEN FName

WHEN @OrderColumn='Title' THEN Title

END

Hope this helps.

JuanBob SSCertifiable Points: 5059 More actions · Answer 3

Thank you very much for the suggestions! If I went the route of dynamic sql, how would I deal with single quotes within the quotes? Such as:

@sql = ' select company, contact, cast(numfield as varchar(20)) as 'Number' from table where company like '%inc%' '

Thanks again!

Farrell Keough SSCoach Points: 17414 More actions · Answer 4

Make sure SET QUOTED_IDENTIFIER { ON | OFF } is set to OFF and use double quotes around your Dynamic SQL statement.

Also, read the following: <A href="Curses'>http://www.sommarskog.se/dynamic_sql.html">Curses & Blessings of Dynamic SQL</A>

(I still can't seem to get it... )

I wasn't born stupid - I had to study.

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

'kay farrell - trying once again - type (all bolded words) exactly in the order given below:

1) start with lesser than sign followed by a - then single space

2) href="http://www.sommarskog.se/dynamic_sql.html"

3) greater than sign

4) Curses and Blessings of Dynamic SQL

5) lesser than sign - /a - greater than sign

..only one space in the whole line (#1)

**ASCII stupid question, get a stupid ANSI !!!**

Farrell Keough SSCoach Points: 17414 More actions · Answer 6

Thanks for being "Committed".

<a href="Curses'>http://www.sommarskog.se/dynamic_sql.html">Curses & Blessings of Dynamic SQL</a>

(I think I must be terminal...)

I wasn't born stupid - I had to study.