SHARE
TWEET

Untitled

a guest Feb 17th, 2017 54 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. I use SQL Server 2012. I have a sample table named 'Table1' with seven columns.
  2.  
  3.     CREATE TABLE TABLE1
  4.     (
  5.         Field1 INT ,
  6.         Field2 INT ,
  7.         Field3 INT ,
  8.         Field4 INT ,
  9.         Field5 INT ,
  10.         Field6 INT ,
  11.         Field7 INT
  12.     )
  13.     GO
  14.  
  15.     INSERT INTO TABLE1 VALUES (1,2,9,5,1,5,85)
  16.     INSERT INTO TABLE1 VALUES (2,6,8,4,1,4,45)
  17.     INSERT INTO TABLE1 VALUES (3,5,7,3,5,6,1)
  18.     INSERT INTO TABLE1 VALUES (4,4,6,1,51,4,1)
  19.     INSERT INTO TABLE1 VALUES (5,5,5,4,7,2,7)
  20.     INSERT INTO TABLE1 VALUES (6,5,4,6,4,7,8)
  21.     INSERT INTO TABLE1 VALUES (7,12,5,3,2,5,3)
  22.     INSERT INTO TABLE1 VALUES (8,1,6,5,9,5,1)
  23.     INSERT INTO TABLE1 VALUES (9,1,13,2,1,7,3)
  24.     INSERT INTO TABLE1 VALUES (10,6,9,3,6,2,6)
  25.     INSERT INTO TABLE1 VALUES (11,2,1,2,8,7,7)
  26.     INSERT INTO TABLE1 VALUES (12,7,6,1,3,3,2)
  27.     INSERT INTO TABLE1 VALUES (13,7,2,6,4,7,1)
  28.     GO
  29.  
  30. I have created the below Stored Procedure, This SP is able to query data considering the asked order by column.
  31. I can query my table with each possibility of single column and kind of order by (ASC or Desc).
  32.  
  33.  
  34.     CREATE Procedure ProceName
  35.     (
  36.         @OrderByField INT = 1,
  37.         @OrderDirection INT = 0 -- 0 = Asc , 1 = Desc
  38.     )  
  39.     As  
  40.     Begin  
  41.             SELECT
  42.                     *  
  43.             FROM Table1
  44.             ORDER BY
  45.                     CASE WHEN @OrderDirection=0 AND @OrderByField=1 THEN Field1 END ASC,
  46.                     CASE WHEN @OrderDirection=0 AND @OrderByField=2 THEN Field2 END ASC,
  47.                     CASE WHEN @OrderDirection=0 AND @OrderByField=3 THEN Field3 END ASC,
  48.                     CASE WHEN @OrderDirection=0 AND @OrderByField=4 THEN Field4 END ASC,
  49.                     CASE WHEN @OrderDirection=0 AND @OrderByField=5 THEN Field5 END ASC,
  50.                     CASE WHEN @OrderDirection=0 AND @OrderByField=6 THEN Field6 END ASC,
  51.                     CASE WHEN @OrderDirection=0 AND @OrderByField=7 THEN Field7 END ASC,
  52.  
  53.                     CASE WHEN @OrderDirection=1 AND @OrderByField=1 THEN Field1 END DESC,
  54.                     CASE WHEN @OrderDirection=1 AND @OrderByField=2 THEN Field2 END DESC,
  55.                     CASE WHEN @OrderDirection=1 AND @OrderByField=3 THEN Field3 END DESC,
  56.                     CASE WHEN @OrderDirection=1 AND @OrderByField=4 THEN Field4 END DESC,
  57.                     CASE WHEN @OrderDirection=1 AND @OrderByField=5 THEN Field5 END DESC,
  58.                     CASE WHEN @OrderDirection=1 AND @OrderByField=6 THEN Field6 END DESC,
  59.                     CASE WHEN @OrderDirection=1 AND @OrderByField=7 THEN Field7 END DESC
  60.     End  
  61.     GO
  62.  
  63.     EXECUTE ProceName  @OrderByField=1, @OrderDirection=0
  64.     EXECUTE ProceName  @OrderByField=6, @OrderDirection=1
  65.  
  66. Now I need to change this sp for accepting multi columns as order by series columns. They can be pass by name or by order of columns.
  67. In this case i should be able to execute my SP like below command:
  68.  
  69.     EXECUTE ProceName  @OrderByField='6,7,2', @OrderDirection='0,1,1'
  70.  
  71. How can I achive this gool with out using the sp_executesql (Dynamic Query)?
RAW Paste Data
Top