SQL Server Performance Forum – Threads Archive

passing parameter to a SELECT query for ORDERBY

Hello Sir/s, I am trying to send a parameter to a SELECT query for ORDERBY <>
For eg :-
SELECT cusno,cusname,tel1,tel2 from customer ORDER BY cusno
SELECT cusno,cusname,tel1,tel2 from customer ORDER BY cusname
SELECT cusno,cusname,tel1,tel2 from customer ORDER BY tel1 For the above examples each time my ORDER BY field changes as per the query requirement.
Now I need a help to pass a parameter from my VB code so that I can write only 1 stored procedure like :- declare @mVar varchar(20)
SELECT cusno,cusname,tel1,tel2 from customer ORDER BY @mVar Write now it is not working and I am wrote 3 stored procedures for this. CAN SOMEBODY HELP ME TO SORT THIS ISSUE ? Thanks and regards
Rajkumar

---

You need to use dynamic query and run this
Read about dynamic queries in BOL. You can try this logic alter procedure proc_name(@mVar varchar(20))
as
SET NOCOUNT ON
declare @sqlcmd nvarchar(4000)
set @sqlcmd=’SELECT cusno,cusname,tel1,tel2 FROM customer ORDER BY ‘[email protected] exec sp_executesql @sqlcmd

---

Hi
Sir, Tx for the code. Its working fine. Can u pls advice where I can incorporate my @mTextSearch along with @sqlcmd pls
my code :- crea proc cust_search_code @mTextSerach Varchar(100)
as
select customer,name,affress5,address6
from slcustm with (nolock)
where upper(customer) like @mTextSerach
order by customer
Here I succeeded to pass a parameter to the ORDER BY by creating a dynamic query suggested by you. But facing trouble to incorporate my @mtextSearch. pls help Thanks in advance

---

Read more on Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html Madhivanan Failing to plan is Planning to fail

---

Another solution: SELECT cusno,cusname,tel1,tel2
from customer
ORDER BY case (@mVar) when ‘cusno’ then cusno end,
case (@mvar) when ‘cusname’ then cusName end,
case (@mVar) when ‘tel1’ then tel1 end

---

Depending on how many possible variations there are, I would probably do something like
CREATE PROCEDURE cust_search_code (@OrderByAlternative INT, some other parameter)
AS
IF @OrderByAlternative = 1
BEGIN
….
ELSE…. Anyway, I wouldn’t use dynamic SQL for this rather simple case. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

---

]]>