Dynamic ORDER BY in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic ORDER BY in stored procedure

Author

Topic

ias0nas
Starting Member

36 Posts

Posted - 2008-04-28 : 09:41:29

Hello,

I have this stored procedure:
SELECT * from purchase
ORDER BY
CASE @OrderBy
WHEN 'traveller_name' THEN cast(traveller_name as nvarchar(100))
WHEN 'canceled' THEN cast(canceled as bit)
END

the @OrderBy is a nvarchar(100) parameter
traveller_name is an nvarchar(100) field
canceled is a bit field

When I execute the stored procedure it works fine until I execute it sorting by canceled field. After that, I cannot sort it again using the traveller_name field. I get this eror:
Conversion failed when converting the nvarchar value 'Jason' to data type bit.
(Jason is a record in the traveller_name)
Removing the castings and sorting by just the column name does not help.

Any ideas?
Thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 09:43:14

Try using WITH recompile for procedure creation.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-28 : 09:48:01

Can you post some sample data with expected result?

Madhivanan

Failing to plan is Planning to fail

ias0nas
Starting Member

36 Posts

Posted - 2008-04-28 : 10:10:59

Thank you for your replies,

Pesco, I have tried the recompile option, it still gives me the same error.

Madhivanan, here is the table definition and a table row below if I execute the stored procedure ordering it by canceled field:
purchase_id int
identifier_company nvarchar(2)
identifier_number int
raised int
date_raised datetime
traveller_name nvarchar(100)
travel_date datetime
disb_overhead char(20)
recoverable bit
company int
department int
gl_code nvarchar(20)
supplier nvarchar(100)
canceled bit
posted_progression bit
dispute_reason nvarchar(100)
invoice_number int

7 UK 4 116 2008-04-28 10:14:39.067 NULL Disbursement 1 1 1 45 SoftCat 0 NULL NULL NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 10:12:52

Don't you need a ELSE case option? Just in case Cancelled is misspelled?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 10:13:49

Or always cast to varchar? Bit will anyway cast to '0' and '1' which has the same meaning.

SELECT * from purchase
ORDER BY
CASE @OrderBy
WHEN 'traveller_name' THEN cast(traveller_name as nvarchar(100))
WHEN 'canceled' THEN cast(canceled as nvarchar(100))
END

E 12°55'05.25"
N 56°04'39.16"

ias0nas
Starting Member

36 Posts

Posted - 2008-04-28 : 10:22:39

Yes thank you!

Having both recompile and converting all of the values to nvarchar seems to work fine.
The ELSE is not really necessery, it wont wort it if nothing matches

Thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 10:32:19

I don't think you need recompile when both cases are having same datatype.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources