asked on

Stored Procedure - Passing order by string as parameter

hi
i want to order the record dynamically..want to pass the order by string at runtime to the SP.

tried with the code below.. it doesn't work..
select clentname,add1 from client
order by ' + @OrderBy + '

the @orderby parameter has the fieldname..(clientname or add1)

peterdownes

TRY:

EXEC ('select clentname, add1 from client order by ' + @OrderBy)

enmaga

Hi,
Try this code. This will work.
I tried to use the variable @fldname in the order by clause. it gave me an error. Finally I did like this. This code is ok and feasible only when few order by changes are there. I mean when order by clause changes twice or thrice. Mean while, let me see whether we can pass a variable and achieve the same thing n number of times.

create procedure deleteme @fldname varchar(10)
as
declare @fld varchar(10)
select @fld=@fldname
if @fld ='clientname'
select * from tablename order by clientname
if @fld = 'add1'
select * from tablename order by add1
return

ruperts

You have to concatinate the string first, that's why answer 1 doesn't work. And answer 2 requires a separate if statement for every field, and if you want to order by 2 fields - well the combinations go on for ever.

i.e

create proc yada
(
@orderby nvarchar(20)
)
as
declare @temp nvarchar(200)
set @temp = 'select * from tblBlah order by ' + @orderby
exec(@temp)
go

--example usage:
yada 'fieldA desc,fieldC'
--or even
yada 'fieldA'

ASKER CERTIFIED SOLUTION

peterdownes

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ruperts

This is true, I'm trying to think why I had a problem with the exec statement a while back.. Never mind, ignore my ramblings.

chandua

ASKER

hi tried foll query it still doesn't work. the diff is iam using a where clause.
if i take out the where clause the order by workss fine
CREATE PROCEDURE dbo.sp_test
@orderby varchar(30),
@Country varchar(50)
as
exec('select * from tblcountry where country=@country order by' + @orderby)

ruperts

CREATE PROCEDURE dbo.sp_test
@orderby varchar(30),
@Country varchar(50)
as
exec('select * from tblcountry where country=''' + @country + ''' order by ' + @orderby)

Cut and paste that...

chandua

ASKER

it still doesn't work fine for me if i have many parameters to my query.. i tried the same method for the foll query, but this one is for passing parameter for the Database name

CREATE PROCEDURE dbo.SPRetUser
@DBName as varchar(20)
AS
exec('select * from ''' + @DBName + '''+tblUser')
GO

my query shld look somethin like this

select * from SOSCase.Dbo.tblUser
pls help me to get the above query

i have increased the points.. to 100 as i feel i am asking too many questions for 15 points.

peterdownes

OK, this query should be:

exec('select * from ' + @DBName + '.tblUser')

if you pass SOSCase.Dbo in @DBName.

Regards.

chandua

ASKER

okie thanks. it works fine for the DBName. but my previous query for Order By still doesn't work.
here is the code again..

___________________________________________________________
CREATE PROCEDURE dbo.SPTest
@Fname varchar(50),
@FromDate smalldatetime,
@ToDate smalldatetime,
@selection varchar(50)
As
exec('select * from tblUser where
UserFName = like '''+ @Fname + ''' + ''' + '%' + ''' and
UserBirthDate between ''' + @FromDate + ''' and ''' + @ToDate + '''
order by ' + @selection)___________________________________________________________

it works well for other parameters except the date, for example:
SPTest '','01/01/1900','01/01/2010','UserFName'
it will always give me the error"
Server: Msg 295, Level 16, State 3, Line 1
Syntax error converting character string to smalldatetime data type.

pls help me to find the solution, thanks a lot

peterdownes

Try:

CREATE PROCEDURE SPTest
@Fname varchar(50),
@FromDate smalldatetime,
@ToDate smalldatetime,
@Selection varchar(50)
AS
DECLARE @strTmp varchar(800)

SET @strTmp = 'select * from tblUser where UserFName like ' + '"' + @Fname + '%" AND UserBirthDate BETWEEN "' + CONVERT(varchar, @FromDate, 106) + '" AND "' + CONVERT(varchar, @ToDate, 106) + '" ORDER BY ' + @Selection

EXEC (@strTmp)

chandua

ASKER

hi peterdownes,

i think i need to understand why so many quotes are passed.
b'cos for diff datatype its giving errors. lastime it was for date parameter & now if i have a integer (int) value. it gives me the error.
here is the code iam trying

CREATE PROCEDURE dbo.SPtest10
@CountID int
AS
declare @string varchar(500)
set @string = 'select * from tblCurrency
where countid=''' + @CountID + ''' '
exec(@string)
GO

pls help...

nigelrivett

Perhaps you would like to reject the proposed answer.
It is rare to lock questions on this forum and they do not get looked at often.

I haven't bothered to read the thread as it is locked but:

Only character variables need to have the value enclosed in quotes.

so

set @string = 'select * from tblCurrency
where string = ''' + @var + ''' '

set @string = 'select * from tblCurrency
where number = ' + @var

will give (for 123 in the @var)
select * from tblCurrency where string = '123'
select * from tblCurrency where number = 123

For your original question
>> select clentname,add1 from client
>> order by ' + @OrderBy + '
>> @orderby (clientname or add1)

select clentname,add1 from client
order by case when @OrderBy = 'clientname' then clientname else null end, add1

or if they are the same datatype
select clentname,add1 from client
order by case when @OrderBy = 'clientname' then clientname else add1 end

peterdownes

As nigelrivett has explained, you only need to place quotes around string values.

Well nearly!

As you can see from you SPTest with smalldatetime parameters, I converted them to 106 format -> dd mon yyyy. So you also need to place quotes around this. If you converted it to say dd/mm/yyyy format, the quotes wouldn't of been necessary.

As ruperts explain earlier, nigelrivett's CASE statement means you need to address all possiblilties, not just clientname and add1. If this is what you want to do, then it removes the need to use the EXEC statement, and therefore all those confusing quotes.

Regards.

Moondancer

Moondancer

EXPERT INPUT PLEASE ..... SINCE CHANDUA CLEARLY IGNORES REQUESTS TO MANAGE QUESTIONS.

I WILL CLOSE THIS, TELL ME WHOM TO AWARD; LET ME KNOW IF SOME POINT SPLITS WOULD BE MORE APPROPRIATE OR ONE RESPONSE IS TO BE AWARDED.

THANKS,

MOONDANCER - EE MODERATOR

Moondancer

Zero response from ANYONE, finalized.
Moondancer - EE Moderator