October 12, 2005 at 12:11 pm
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!
October 12, 2005 at 12:25 pm
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
October 12, 2005 at 12:25 pm
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.
October 12, 2005 at 3:10 pm
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!
October 12, 2005 at 3:22 pm
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.
October 12, 2005 at 3:37 pm
'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 !!!**
October 12, 2005 at 3:49 pm
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply