17 June, 2009

Sort Expression as a parameter to Stored Procedure

How to send the sort expression as a parameter to Stored Procedure?

This is the problem that I faced in one of my recent tasks. When I was given this work,
without much thought, started with the following lines.


CREATE PROCEDURE GetProcedure
@
sortExp varchar(25)
AS
select ProcedureID,Items,[Value] from Procedure
order by @sortExp DESC


When I tried to run this stored procedure, I got an unexpected error saying,

Msg 1008, Level 16, State 1, Procedure GetProcedure, Line 5
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.


Then, I started to think about the error and soon I traced the fault.
The problem is that- SQL Server is not recognizing the given parameter as a table column. By default all the parameters are considered to be of some data type. They can't be converted into type 'Column'. As "Order by" statement needs a column name, server is throwing an error.

What is the Solution?
The solution for the problem is using a "CASE" statement with the parameter.
Then the procedure will look like

CREATE PROCEDURE GetProcedure
@
sortExp varchar(25)
AS
select ProcedureID,Items,[Value] from Procedure
order by CASE @sortExp
When '
ProcedureID' then
ProcedureID
When '
Items' then
Items
When '
Value' then
Value
End
DESC

Now,What if you want to send another parameter for Direction...?
then, you have to check that in if-else loop or another CASE Statement.

No comments:

Post a Comment