Order by a @tempvar - query selection using CASE

Last Post 19 Jan 2012 12:42 PM by russellb. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages Informative
Derek Colley
New Member
New Member

--
23 Nov 2011 06:01 AM
Here's my contribution.  A script to allow you to run a query and order by a temporary variable - useful when passing in a parameter or accepting user input.

-- Written by Derek Colley, C* M* S* Ltd.
-- Swap out any and all col names as required and actions taken in each query as applicable
-- Doesn't work as a dataset (sadly) in SSRS due to sp_executesql output going to null
-- Purpose: Based on a single input param, select a query to run, then order by a particular column.
-- Invented because SQL Server doesn't allow ORDER BY @variable

declare @query1 VARCHAR(MAX)
declare @query2 VARCHAR(MAX)
declare @query3 VARCHAR(MAX)
declare @query4 VARCHAR(MAX)
declare @query5 VARCHAR(MAX)

IF EXISTS (SELECT * from tempdb..sysobjects WHERE id = OBJECT_ID('tempdb.dbo.#fooTemp'))
  DROP TABLE #fooTemp
  CREATE TABLE #fooTemp(pk_contract_id int, pk_company_id int, companyname varchar(max), RENEWAL_DATE smalldatetime)

SET @query1 = ' query goes here ORDER BY col1'
SET @query2 = ' query goes here ORDER BY col2'
SET @query3 = ' query goes here ORDER BY col3'
SET @query4 = ' query goes here ORDER BY col4'
SET @query5 = ' query goes here ORDER BY col5'

declare @inputParameter VARCHAR(MAX)
set @inputParameter = ''
declare @fooQuery NVARCHAR(MAX)
SET @fooQuery =
  CASE @inputParameter
    WHEN '' THEN @query1
    WHEN '' THEN @query2
    WHEN '' THEN @query3
    WHEN '' THEN @query4
    WHEN '' THEN @query5
 END

exec sp_executesql @fooQuery
Brett
New Member
New Member

--
17 Jan 2012 12:01 PM
Really? Ever hear of SQL Injection?
russellb
New Member
New Member

--
19 Jan 2012 12:42 PM
Yeah, there are much better ways to do that.


Acceptable Use Policy
---