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
|