Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149

    Unanswered: Dynamic Order By

    Hi All-

    I need to dynamically sort my result set depending on a sort parameter.

    CREATE TABLE EMP
    (EmpId INT,
    EmpName VARCHAR(100),
    JoinDate DATETIME)

    INSERT INTO EMP VALUES (1, 'ZZZ', '01/01/01')
    INSERT INTO EMP VALUES (2, 'XXX', '01/01/02')
    INSERT INTO EMP VALUES (3, 'AAA', '01/01/03')

    I tried:

    DECLARE @Filter VARCHAR(100)
    SET @Filter = 'EmpName'

    SELECT *
    FROM EMP
    ORDER BY CASE WHEN @Filter = 'EmpId' THEN EmpId
    WHEN @Filter = 'EmpName' THEN EmpName
    ELSE JoinDate
    END

    I get an error "Syntax error converting datetime from character string."

    (It works if I try EmpId though.)

    Any help is greatly appreciated.

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    How about :
    Code:
    CREATE TABLE EMP 
    (EmpId INT, 
    EmpName VARCHAR(100), 
    JoinDate DATETIME)
    
    INSERT INTO EMP VALUES (1, 'ZZZ', '01/01/01')
    INSERT INTO EMP VALUES (2, 'XXX', '01/01/02')
    INSERT INTO EMP VALUES (3, 'AAA', '01/01/03')
    
    DECLARE @Filter VARCHAR(100)
    declare @query varchar(150)
    SET @Filter = 'EmpName'
    select @query = 'SELECT * FROM EMP ORDER BY '+ @filter
    exec (query)
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Or non dynamic sql

    Code:
    USE Northwind
    GO
    
    CREATE TABLE EMP (
    	  EmpId int 
    	, EmpName varchar(100) 
    	, JoinDate datetime)
    GO
    
    INSERT INTO EMP (EmpId, EmpName, JoinDate) 
         SELECT 1, 'ZZZ', '01/01/01' UNION ALL
         SELECT 2, 'XXX', '01/01/02' UNION ALL
         SELECT 3, 'AAA', '01/01/03'
    
    DECLARE @Filter varchar(100)
     SELECT 
    @Filter = 'EmpName'
    --@Filter = 'EmpId'
    --@Filter = 'xxx'
    
    SELECT *
    FROM EMP
    ORDER BY CASE   WHEN @Filter = 'EmpId'      THEN CONVERT(varbinary,EmpId)
    	           WHEN @Filter = 'EmpName' THEN CONVERT(varbinary,EmpName)
    				    ELSE CONVERT(varbinary,JoinDate)	
    	 END
    GO
    
    DROP TABLE EMP
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    Hi Enigma-

    Thnx for your response. But I don't want to use Dynamic SQL.


    Originally posted by Enigma
    How about :
    Code:
    CREATE TABLE EMP 
    (EmpId INT, 
    EmpName VARCHAR(100), 
    JoinDate DATETIME)
    
    INSERT INTO EMP VALUES (1, 'ZZZ', '01/01/01')
    INSERT INTO EMP VALUES (2, 'XXX', '01/01/02')
    INSERT INTO EMP VALUES (3, 'AAA', '01/01/03')
    
    DECLARE @Filter VARCHAR(100)
    declare @query varchar(150)
    SET @Filter = 'EmpName'
    select @query = 'SELECT * FROM EMP ORDER BY '+ @filter
    exec (query)

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here's my shot in the dark. Similiar to brett's.

    SELECT *
    FROM EMP
    ORDER BY CASE WHEN @Filter = 'EmpId' THEN right('00000000' + cast(EmpID as varchar(8)), 8)
    WHEN @Filter = 'EmpName' THEN EmpName
    ELSE convert(varchar(20), JoinDate, 120)
    END

    blindman

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    Thanks Brett. It works. I am still wondering what was SQL Server trying to do in the code I wrote. The error message is interesting.

    - Sarat

    Originally posted by Brett Kaiser
    Or non dynamic sql

    Code:
    USE Northwind
    GO
    
    CREATE TABLE EMP (
    	  EmpId int 
    	, EmpName varchar(100) 
    	, JoinDate datetime)
    GO
    
    INSERT INTO EMP (EmpId, EmpName, JoinDate) 
         SELECT 1, 'ZZZ', '01/01/01' UNION ALL
         SELECT 2, 'XXX', '01/01/02' UNION ALL
         SELECT 3, 'AAA', '01/01/03'
    
    DECLARE @Filter varchar(100)
     SELECT 
    @Filter = 'EmpName'
    --@Filter = 'EmpId'
    --@Filter = 'xxx'
    
    SELECT *
    FROM EMP
    ORDER BY CASE   WHEN @Filter = 'EmpId'      THEN CONVERT(varbinary,EmpId)
    	           WHEN @Filter = 'EmpName' THEN CONVERT(varbinary,EmpName)
    				    ELSE CONVERT(varbinary,JoinDate)	
    	 END
    GO
    
    DROP TABLE EMP
    GO

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    brett's is better. go with the varbinary.

    blindman

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Credit, where credit is due:

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28182

    Rob's the man

    And btw, the reason you got the error is because you can't mix and match datatypes...

    good luck
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by sbaru
    Hi Enigma-

    Thnx for your response. But I don't want to use Dynamic SQL.
    Oh and btw

    hip hip hooray

    sorry...i feel better now...you don't often see such reasonable assesments!
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •