sorting by column name passed as a variable

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

SQL Server Development (2000)

sorting by column name passed as a variable

Author

Topic

PGG123
Yak Posting Veteran

55 Posts

Posted - 2003-06-17 : 15:26:34

I'm trying to do an order by depending on the column name passed to the stored proc below.

SELECT DISTINCT txtCostCenter, txtDescription
FROM Element1_Codes EC inner join CodePfMulti1 CP on
EC.lngIndex = CP.lngCodeID
WHERE CP.lngProfID = @profID AND EC.lngIndex > 0 AND ((txtDescription LIKE @text + '%') OR (txtCostCenter LIKE @text + '%'))
order by @sortfield

I get this error:
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

I don't know what it means. I think I can rewrite the stored proc by using dynamic sql. But is there a better way?

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-17 : 15:28:34

order by case when @sortfield = col1 then col1 else null end ,
case when @sortfield = col2 then col2 else null end ,
case when @sortfield = col3 then col3 else null end ,
case when @sortfield = col4 then col4 else null end

==========================================
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.

PGG123
Yak Posting Veteran

55 Posts

Posted - 2003-06-17 : 15:43:56

what would be col1 etc.? should i replace col1 etc in the stored proc with the name of the columns to sort by? I only want to order by either txtDescription or txtCostCenter.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-17 : 15:47:10

col1, col2 are just generic column names. Replace them with your column names. Remove col3 and col4 lines since you only have two things to sort by. Nigel was just showing you an example.

Tara

PGG123
Yak Posting Veteran

55 Posts

Posted - 2003-06-17 : 16:42:39

here's my modified stored proc:
SELECT DISTINCT txtCostCenter, txtDescription, 'ET' as eTable
FROM Element1_Codes EC inner join CodePfMulti1 CP on
EC.lngIndex = CP.lngCodeID
WHERE CP.lngProfID = @profID AND EC.lngIndex > 0 AND ((txtDescription LIKE @text + '%') OR (txtCostCenter LIKE @text + '%'))
order by case when @sortfield = 'txtCostCenter' then 'txtCostCenter' else null end ,
case when @sortfield = 'txtDescription' then 'txtDescription' else null end

It still gives me the same error.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-17 : 17:04:01

Remove the single quotes around the column names just like the example.

SELECT DISTINCT txtCostCenter, txtDescription, 'ET' as eTable
FROM Element1_Codes EC inner join CodePfMulti1 CP on
EC.lngIndex = CP.lngCodeID
WHERE CP.lngProfID = @profID AND EC.lngIndex > 0 AND ((txtDescription LIKE @text + '%') OR (txtCostCenter LIKE @text + '%'))
order by case when @sortfield = txtCostCenter then txtCostCenter else null end ,
case when @sortfield = txtDescription then txtDescription else null end

Does it work now?

Tara

PGG123
Yak Posting Veteran

55 Posts

Posted - 2003-06-17 : 17:56:56

I get invalid object names 'Element1_Codes' and 'CodePfmulti1' error.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-17 : 18:05:03

Well do those two tables exist in the database that you are using? The error is coming from your FROM statement. Verify that the objects exist.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources