Parameterized sortorder 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 2000 Forums

Transact-SQL (2000)

Parameterized sortorder in stored procedure

Author

Topic

Nico
Starting Member

18 Posts

Posted - 2002-08-19 : 08:21:18

Hello,

I'm programming a .NET site in C# and I would like to use stored procedures that take a parameter for its sort order.

Example:

CREATE PROCEDURE sp_Example
(
@SortOrder Varchar(20)
)
AS
SELECT *
FROM table
ORDER BY @SortOrder

// C# code
// create command with stored procedure
SqlCommand cmd = new SqlCommand( "sp_Example", con );
cmd.CommandType = CommandType.StoredProcedure;
// add params
cmd.Parameters.Add( "@SortOrder", "table_to_be_sorted" );

The problem is... it doesn't work.
I've tried ORDER BY '@SortOrder', and I've tried passing the table number instead of the table name, but it appears as if it just isn't possible to create a dynamic sortorder.

Any ideas? (other than creating a stored procedure for every possible sort order)

Kind regards,
Nico R.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-19 : 08:24:44

you can do something like

order by case @sortcol when 1 then col1 when 2 then col2 when ...end

may have problems with datatypes in this though so another option is

order by
case @sortcol when 1 then col1 else null end ,
case @sortcol when 2 then col2 else null end ,
case @sortcol when 3 then col3 else null end ,
or a mixture of both

you can also use dynamic sql if you don't want to hard code the column names.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Nico
Starting Member

18 Posts

Posted - 2002-08-19 : 10:46:15

Many thanks!

Now I'm facing the same problem with ASC/DESC.

The following appears logical, but doesn't work.

order by
case @sortcol when 1 then col1 ... end
case @sortdir when 'asc' then asc when 'desc' then desc end

I tried nesting the case statements, but that didn't work either.

It's not that crucial since I can always make 2 stored procedures, one for asc, one for desc... but I'm getting curious now ;)

Kind regards,
Nico R.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-19 : 11:07:48

try

case when @sortcol = 1 then case @sortdir when 'asc' then col1 asc else colu1 desc end else null end

or use positives and negatives to determine sort order simple as negating the values

case @sortcol when 1 then col1 asc when -1 then col1 desc else null end
case @sortcol when 2 then col2 asc when -2 then col2 desc else null end

Edited by - onamuji on 08/19/2002 11:13:38

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-19 : 11:16:14

you can't put asc and dec inside the case statement

case @sortdir when 'asc' then
case @sortcol when 1 then case col1 ... end
else null end asc ,
case @sortdir when 'desc' then
case @sortcol when 1 then case col1 ... end
else null end desc

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Nico
Starting Member

18 Posts

Posted - 2002-08-19 : 11:17:28

I have tried nesting already... unfortunately it doesn't work.

Incorrect syntax near the keyword 'asc'.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-19 : 11:21:30

Ha I could have sworn I have done that before .. .should have tested it before i posted :p

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-19 : 11:23:35

so here's another possiblity ... use Dynamic SQL ... for your last select to a select ... into and then write the dynamic sql to select from the table ... probably have to be a global temp table or something like that ...

@sql = 'select * from ##sortTable order by ' + case @sort when 1 then 'col1 asc' when -1 then 'col1 desc' else 'null' end

exec (@sql)

...

Nico
Starting Member

18 Posts

Posted - 2002-08-20 : 03:40:08

Thanks for the replies.

Nr's solution works just fine :) I tried something similar myself, but forgot the second case @sortdir.

(I have no experience with Dynamic SQL and therefore haven't tried out Onamuji's solution yet).

Kind regards,
Nico R.

Nico
Starting Member

18 Posts

Posted - 2002-10-14 : 05:22:05

Running into problems with the datatypes.

1.)
case @sortVolgorde when 'ASC' then
case @sortArgument
when 'ID' then Topic.id
when 'Titel' then Topic.titel
else null end asc ,
case @sortVolgorde when 'DESC' then
case @sortArgument
when 'ID' then Topic.id
when 'Titel' then Topic.titel
else null end desc

This does not work, because topic.id is an int and topic.titel is a varchar.

2.)
case @sortArgument when 'ID' then Topic.id else null end,
case @sortArgument when 'Titel' then Topic.titel else null end

This works, but note that I left out the sort order part.

3.)
case @sortVolgorde when 'ASC' then
case @sortArgument when 'ID' then Topic.id else null end, (<- line X)
case @sortArgument when 'Titel' then Topic.titel else null end
else null end asc ,
case @sortVolgorde when 'DESC' then
case @sortArgument when 'ID' then Topic.id else null end,
case @sortArgument when 'Titel' then Topic.titel else null end
else null end desc

Logic suggest this would be the solution, but I get the following error: "Line x: Incorrect syntax near ','.

Any suggestions?

Nico
Starting Member

18 Posts

Posted - 2002-10-14 : 05:47:31

And a/the solution is:

if @sortVolgorde='DESC'
begin
SELECT ...
FROM ...
WHERE ...
ORDER BY
case @sortArgument when 'ID' then Topic.id else null end,
case @sortArgument when 'Titel' then Topic.titel else null end
desc
end
else
begin
SELECT ...
FROM ...
WHERE ...
ORDER BY
case @sortArgument when 'ID' then Topic.id else null end,
case @sortArgument when 'Titel' then Topic.titel else null end
asc
end

Edited:
My mistake. It compiled, but it doesn't work.
This query always sorts in ascending order.
I think I'll just write 2 stored procedures instead.

Edited by - Nico on 10/14/2002 06:24:23

Edited:
Of course the following doesn't work (properly):
case @sortArgument when 'ID' then Topic.id else null end,
case @sortArgument when 'Titel' then Topic.titel else null end
asc
It should have been:
case @sortArgument when 'ID' then Topic.id else null end asc,
case @sortArgument when 'Titel' then Topic.titel else null end asc

Edited by - Nico on 10/14/2002 06:32:34

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-10-14 : 16:51:50

Nico, did you try CASTing or CONVERTing the columns in your CASE statements to be the same datatype?

Nico
Starting Member

18 Posts

Posted - 2002-10-21 : 03:43:29

No, I did not try CAST or CONVERT.
That probably would have worked better than my own solution.
I'll keep it in mind for the next time I run into a similar problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources