|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
"Order By" Parameter to a Stored ProcedureCan I pass a Parameter to Stored Procedure which will be the ORDER BY column
to be used in a SELECT? If so, what's the syntax? Alex Alex,
You can't do this directly, but you can do something like the following: If the parameter is @ordCol, put this at the end of your SELECT statement: ORDER BY case when @ordCol = 'ThisColumn' then ThisColumn end, case when @ordCol = 'ThatColumn' then ThatColumn end, case when @ordCol = 'OtherColumn' then OtherColumn end See this article for more examples: http://databases.aspfaq.com/database/how-do-i-use-a-variable-in-an-order-by-clause.html -- Steve Kass -- Drew University -- http://www.stevekass.com Alex Maghen wrote: Show quote >Can I pass a Parameter to Stored Procedure which will be the ORDER BY column >to be used in a SELECT? If so, what's the syntax? > >Alex > > This is incredibly helpful, thanks. Just one more thing?
What if I want to have one of the CASEs be more than one Order By column, for example... case when @ordCol = 'ThisColumn' then ThisColumn, THATCOLUMN end,... If I try to do this, I get a syntax error because of the Comma. Any ideas? Alex Show quote "Steve Kass" wrote: > Alex, > > You can't do this directly, but you can do something like the following: > > If the parameter is @ordCol, put this at the end of your SELECT > statement: > > ORDER BY > case when @ordCol = 'ThisColumn' then ThisColumn end, > case when @ordCol = 'ThatColumn' then ThatColumn end, > case when @ordCol = 'OtherColumn' then OtherColumn end > > See this article for more examples: > http://databases.aspfaq.com/database/how-do-i-use-a-variable-in-an-order-by-clause.html > > -- Steve Kass > -- Drew University > -- http://www.stevekass.com > > > Alex Maghen wrote: > > >Can I pass a Parameter to Stored Procedure which will be the ORDER BY column > >to be used in a SELECT? If so, what's the syntax? > > > >Alex > > > > > Okay, well actually, I jumped the gun. I seem to be having some strange
problem. Can you tell me why the following procedure would be returning the error... 'Conversion failed when converting datetime from character string.' no matter what I provide as the parameter? ============================= ALTER PROCEDURE dbo.AxT_UsersGetAll ( @OrderBy varchar(50) = '' ) AS BEGIN Select * from UsersT ORDER BY case upper(@OrderBy) when 'USERNAME' then Username when 'LASTNAME' then LastName when 'FIRSTNAME' then FirstName when 'ISINTERNAL' then IsInternal when 'ACCESSSTRING' then AccessString when 'PASSWD' then Passwd when 'DEPARTMENT' then Department when 'TITLE' then Title when 'EADDR' then EAddr when 'BIRTHDATE' then BirthDate else LastName end END ============================== Show quote "Alex Maghen" wrote: > This is incredibly helpful, thanks. Just one more thing? > What if I want to have one of the CASEs be more than one Order By column, > for example... > > case when @ordCol = 'ThisColumn' then ThisColumn, THATCOLUMN end,... > > If I try to do this, I get a syntax error because of the Comma. Any ideas? > > Alex > > > > "Steve Kass" wrote: > > > Alex, > > > > You can't do this directly, but you can do something like the following: > > > > If the parameter is @ordCol, put this at the end of your SELECT > > statement: > > > > ORDER BY > > case when @ordCol = 'ThisColumn' then ThisColumn end, > > case when @ordCol = 'ThatColumn' then ThatColumn end, > > case when @ordCol = 'OtherColumn' then OtherColumn end > > > > See this article for more examples: > > http://databases.aspfaq.com/database/how-do-i-use-a-variable-in-an-order-by-clause.html > > > > -- Steve Kass > > -- Drew University > > -- http://www.stevekass.com > > > > > > Alex Maghen wrote: > > > > >Can I pass a Parameter to Stored Procedure which will be the ORDER BY column > > >to be used in a SELECT? If so, what's the syntax? > > > > > >Alex > > > > > > > > Alex Maghen (AlexMaghen@newsgroup.nospam) writes:
Show quote > Okay, well actually, I jumped the gun. I seem to be having some strange The return type from a CASE expression is always the one and the> problem. Can you tell me why the following procedure would be returning > the error... > > 'Conversion failed when converting datetime from character string.' no > matter what I provide as the parameter? > > Select * from UsersT > ORDER BY > case upper(@OrderBy) > when 'USERNAME' then Username > when 'LASTNAME' then LastName > when 'FIRSTNAME' then FirstName > when 'ISINTERNAL' then IsInternal > when 'ACCESSSTRING' then AccessString > when 'PASSWD' then Passwd > when 'DEPARTMENT' then Department > when 'TITLE' then Title > when 'EADDR' then EAddr > when 'BIRTHDATE' then BirthDate > else LastName > end same, no matter which WHEN branch that is selected. The datatype is determined by SQL Server's datatype precendence, which says that when two types meet, the type with lower precendence is converted to the type with higher precendence. Judging from the column names, all columns are varchar except IsInternal and BirthDate of which the latter is datetime. Datetime has higher precendence than varchar. (See Books Online for full details on datatype precedence). This means that the data type of the CASE expression is datetime. The remedy is two have: ORDER BY CASE upper(@OrderBy) WHEN 'USERNAME' THEN .... END, CASE upper(@OrderBy) WHEN 'ISINTERNAL' THEN IsInternal END, CASE upper(@OrderBy) WHEN 'BirthDate' THEN Birthdate END If you need to support multiple sort columns, you also need multiple CASE expressions. CASE in T-SQL is a scalar expression, not a flow-of- control statement. If you are not well acquainted with it, I encourage you to read about it in Books Online. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx VERY helpful. Thanks! One more little thing. In the syntax you lay out, is it
possible to support some kind of "ELSE" condition? Alex Show quote "Erland Sommarskog" wrote: > Alex Maghen (AlexMaghen@newsgroup.nospam) writes: > > Okay, well actually, I jumped the gun. I seem to be having some strange > > problem. Can you tell me why the following procedure would be returning > > the error... > > > > 'Conversion failed when converting datetime from character string.' no > > matter what I provide as the parameter? > > > > Select * from UsersT > > ORDER BY > > case upper(@OrderBy) > > when 'USERNAME' then Username > > when 'LASTNAME' then LastName > > when 'FIRSTNAME' then FirstName > > when 'ISINTERNAL' then IsInternal > > when 'ACCESSSTRING' then AccessString > > when 'PASSWD' then Passwd > > when 'DEPARTMENT' then Department > > when 'TITLE' then Title > > when 'EADDR' then EAddr > > when 'BIRTHDATE' then BirthDate > > else LastName > > end > > The return type from a CASE expression is always the one and the > same, no matter which WHEN branch that is selected. The datatype > is determined by SQL Server's datatype precendence, which says that > when two types meet, the type with lower precendence is converted > to the type with higher precendence. > > Judging from the column names, all columns are varchar except IsInternal > and BirthDate of which the latter is datetime. Datetime has higher > precendence than varchar. (See Books Online for full details on datatype > precedence). This means that the data type of the CASE expression is > datetime. > > The remedy is two have: > > ORDER BY CASE upper(@OrderBy) WHEN 'USERNAME' THEN .... END, > CASE upper(@OrderBy) WHEN 'ISINTERNAL' THEN IsInternal END, > CASE upper(@OrderBy) WHEN 'BirthDate' THEN Birthdate END > > If you need to support multiple sort columns, you also need multiple > CASE expressions. CASE in T-SQL is a scalar expression, not a flow-of- > control statement. If you are not well acquainted with it, I encourage > you to read about it in Books Online. > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > Alex,
You can order by some default column by adding it as a final ORDER BY item. order by case when upper(@OrderBy) = 'USERNAME' then Username end, case when upper(@OrderBy) = 'LASTNAME' then Lastname end, case when upper(@OrderBy) = 'FIRSTNAME' then Firstname end, row_ID You can also put row_ID into the ELSE part of the CASE expression, but if you do, you will have the same issues about types as with multiple WHEN sections. When a CASE expression has no ELSE part, the ELSE part defaults to NULL. See Books Online for the documentation. SK Alex Maghen wrote: Show quote >VERY helpful. Thanks! One more little thing. In the syntax you lay out, is it >possible to support some kind of "ELSE" condition? > >Alex > >"Erland Sommarskog" wrote: > > > >>Alex Maghen (AlexMaghen@newsgroup.nospam) writes: >> >> >>>Okay, well actually, I jumped the gun. I seem to be having some strange >>>problem. Can you tell me why the following procedure would be returning >>>the error... >>> >>>'Conversion failed when converting datetime from character string.' no >>>matter what I provide as the parameter? >>> >>> Select * from UsersT >>> ORDER BY >>> case upper(@OrderBy) >>> when 'USERNAME' then Username >>> when 'LASTNAME' then LastName >>> when 'FIRSTNAME' then FirstName >>> when 'ISINTERNAL' then IsInternal >>> when 'ACCESSSTRING' then AccessString >>> when 'PASSWD' then Passwd >>> when 'DEPARTMENT' then Department >>> when 'TITLE' then Title >>> when 'EADDR' then EAddr >>> when 'BIRTHDATE' then BirthDate >>> else LastName >>> end >>> >>> >> >>The return type from a CASE expression is always the one and the >>same, no matter which WHEN branch that is selected. The datatype >>is determined by SQL Server's datatype precendence, which says that >>when two types meet, the type with lower precendence is converted >>to the type with higher precendence. >> >>Judging from the column names, all columns are varchar except IsInternal >>and BirthDate of which the latter is datetime. Datetime has higher >>precendence than varchar. (See Books Online for full details on datatype >>precedence). This means that the data type of the CASE expression is >>datetime. >> >>The remedy is two have: >> >> ORDER BY CASE upper(@OrderBy) WHEN 'USERNAME' THEN .... END, >> CASE upper(@OrderBy) WHEN 'ISINTERNAL' THEN IsInternal END, >> CASE upper(@OrderBy) WHEN 'BirthDate' THEN Birthdate END >> >>If you need to support multiple sort columns, you also need multiple >>CASE expressions. CASE in T-SQL is a scalar expression, not a flow-of- >>control statement. If you are not well acquainted with it, I encourage >>you to read about it in Books Online. >> >> >>-- >>Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >> >>Books Online for SQL Server 2005 at >>http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >>Books Online for SQL Server 2000 at >>http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >> >> >>  |
|||||||||||||||||||||||
Other interesting topics