Using dynamic variables in the ORDER BY clause

[Guest_imported]

I want to be able to give one of my applications the ability to have query results sorted based on user input. To do this of course required variables. I can get a dynamic SQL query to work but like below... but having hardcode / 'set' the value makes the whole thing useless.

declare @OrderBy_1 [varchar](20)
declare @sql varchar(1000)

select @OrderBy_1 = 'PSID'
set @sql = "select * from tblPS WHERE PSID = '100' Order By " + @OrderBy_1

exec (@sql)

How can I make @OrderBy_1 dynamically fed by a form input?

Cclabaugh

 

[tlbroadbent]

Pass the criteria to a stored procedure.

Create Procedure usp_Sample @OrderBy_1 varchar(20) As

declare @sql varchar(1000)

Set @sql = "select * from tblPS WHERE PSID = '100' Order By " + @OrderBy_1

exec (@sql)
Go

From your client application you would execute the stored procedure as "Exec usp_Sample 'PSID'" and the SP would accept 'PSID' as the parameter, build the SQL string, execute it and return the record set to the client in the proper order. Terry

http://members.home.net/tlbroadbent/prog.htm

Neither success nor failure is ever final. -Roger Babson

 

[Guest_imported]

Thank you. Your suggestion worked, except that I didn't use 'GO'