USE [StackOverflow2013]
GO
/****** Object: StoredProcedure [dbo].[useDynamicSQLinstead] Script Date: 11/20/2018 8:47:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-----------------------------------------------------------------------------------
-- Written by Cyndi Johnson for use in her 'Behold The Power of Dynamic SQL'
-- session at Pass Summit 2018 on November 9, 2018. Permission for reuse is granted
-- in whole or in part, with attribution. However, if you put this code ( or the
-- code this procedure generates) on a Production server, you are doing so against
-- developer's advice. Do so at your own risk! No warranty is implied.
-----------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[useDynamicSQLinstead]
@query NVARCHAR(MAX) = '',
@parameters AS dbo.parameters READONLY
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID('dbo.tempParams') IS NOT NULL
TRUNCATE TABLE dbo.tempParams;
IF OBJECT_ID('dbo.ParamPivot') IS NOT NULL
DROP TABLE dbo.ParamPivot;
DECLARE
@param NVARCHAR(100),
@id INT,
@fieldname NVARCHAR(100),
@whereclause NVARCHAR(MAX),
@ifstatement NVARCHAR(MAX),
@paramCount INT,
@combinations INT,
@loopcount INT,
@paramcounter INT,
@fullstatement NVARCHAR(MAX) = '',
@setstatement NVARCHAR(MAX) = '',
@casecounter INT = 0;
DECLARE
@bit1 BIT = 0,
@bit2 BIT = 0,
@bit3 BIT = 0,
@bit4 BIT = 0,
@bit5 BIT = 0,
@bit6 BIT = 0,
@bit7 BIT = 0,
@bit8 BIT = 0,
@bit9 BIT = 0,
@bit10 BIT = 0,
@bit11 BIT = 0,
@bit12 BIT = 0,
@bit13 BIT = 0,
@bit14 BIT = 0,
@bit15 BIT = 0,
@bit16 BIT = 0,
@bit17 BIT = 0,
@bit18 BIT = 0,
@bit19 BIT = 0,
@bit20 BIT = 0,
@ParmDefinition NVARCHAR(MAX),
@SetParmDefinition NVARCHAR(MAX);
DECLARE @bitflipper BIT = 0;
DECLARE
@parameter1 NVARCHAR(100),
@parameter2 NVARCHAR(100),
@parameter3 NVARCHAR(100),
@parameter4 NVARCHAR(100),
@parameter5 NVARCHAR(100),
@parameter6 NVARCHAR(100),
@parameter7 NVARCHAR(100),
@parameter8 NVARCHAR(100),
@parameter9 NVARCHAR(100),
@parameter10 NVARCHAR(100),
@parameter11 NVARCHAR(100),
@parameter12 NVARCHAR(100),
@parameter13 NVARCHAR(100),
@parameter14 NVARCHAR(100),
@parameter15 NVARCHAR(100),
@parameter16 NVARCHAR(100),
@parameter17 NVARCHAR(100),
@parameter18 NVARCHAR(100),
@parameter19 NVARCHAR(100),
@parameter20 NVARCHAR(100);
SET @ParmDefinition
= N'@bit1 BIT,
@bit2 BIT,
@bit3 BIT,
@bit4 BIT,
@bit5 BIT,
@bit6 BIT,
@bit7 BIT,
@bit8 BIT,
@bit9 BIT,
@bit10 BIT,
@bit11 BIT,
@bit12 BIT,
@bit13 BIT,
@bit14 BIT,
@bit15 BIT,
@bit16 BIT,
@bit17 BIT,
@bit18 BIT,
@bit19 BIT,
@bit20 BIT';
SET @SetParmDefinition
= N'@param1 NVARCHAR(100) OUT,
@param2 NVARCHAR(100) OUT,
@param3 NVARCHAR(100) OUT,
@param4 NVARCHAR(100) OUT,
@param5 NVARCHAR(100) OUT,
@param6 NVARCHAR(100) OUT,
@param7 NVARCHAR(100) OUT,
@param8 NVARCHAR(100) OUT,
@param9 NVARCHAR(100) OUT,
@param10 NVARCHAR(100) OUT,
@param11 NVARCHAR(100) OUT,
@param12 NVARCHAR(100) OUT,
@param13 NVARCHAR(100) OUT,
@param14 NVARCHAR(100) OUT,
@param15 NVARCHAR(100) OUT,
@param16 NVARCHAR(100) OUT,
@param17 NVARCHAR(100) OUT,
@param18 NVARCHAR(100) OUT,
@param19 NVARCHAR(100) OUT,
@param20 NVARCHAR(100) OUT ';
INSERT INTO dbo.tempParams
(
param,
fieldname,
param_alias,
whereclause,
datatype,
rownum
)
SELECT
param,
fieldname = RIGHT(param, LEN(param) - 1),
param_alias,
whereclause = param_alias + '=' + param,
datatype,
rownum = ROW_NUMBER() OVER (ORDER BY param)
FROM
@parameters;
-- Demo query
--SELECT 'dbo.tempParams',* FROM dbo.tempParams ORDER BY rownum;
SELECT
@paramCount = COUNT(1)
FROM
dbo.tempParams;
SET @paramcounter = 1;
WHILE @paramcounter <= @paramCount
BEGIN
SELECT
@setstatement
= N'SELECT @param' + LTRIM(RTRIM(CAST(@paramcounter AS CHAR(100)))) + N' = '
+ N'fieldname FROM dbo.tempParams WHERE rownum=' + LTRIM(RTRIM(CAST(@paramcounter AS CHAR(100))));
EXECUTE sp_executesql
@setstatement,
@SetParmDefinition,
@parameter1 OUT,
@parameter2 OUT,
@parameter3 OUT,
@parameter4 OUT,
@parameter5 OUT,
@parameter6 OUT,
@parameter7 OUT,
@parameter8 OUT,
@parameter9 OUT,
@parameter10 OUT,
@parameter11 OUT,
@parameter12 OUT,
@parameter13 OUT,
@parameter14 OUT,
@parameter15 OUT,
@parameter16 OUT,
@parameter17 OUT,
@parameter18 OUT,
@parameter19 OUT,
@parameter20 OUT;
SET @paramcounter = @paramcounter + 1;
END;
-- Figure out how many combinations there are
-- Each parameter can be null or non-null, so two different
-- values for the purposes of this code
SET @combinations = POWER(2, @paramCount);
-- Dynamically create table to help iterate through
-- all the cases (@combinations of parameter usage)
DECLARE
@cols NVARCHAR(MAX),
@cols2 NVARCHAR(MAX),
@createstatement NVARCHAR(MAX),
@insertstatement NVARCHAR(MAX),
@insertvalues NVARCHAR(MAX) = '';
SELECT
@cols = STUFF(
(
SELECT
DISTINCT
', ' + t2.fieldname + ' bit '
FROM
dbo.tempParams AS t2
ORDER BY
', ' + t2.fieldname + ' bit '
FOR XML PATH('')
),
1,
2,
' '
) + N' ';
SELECT
@cols2 = STUFF(
(
SELECT
DISTINCT
', ' + t2.fieldname + ' '
FROM
dbo.tempParams AS t2
ORDER BY
', ' + t2.fieldname + ' '
FOR XML PATH('')
),
1,
2,
' '
) + N' ';
SET @cols = @cols + N', id_num INT IDENTITY(1,1) ';
SET @createstatement = N'CREATE TABLE dbo.ParamPivot ( ' + @cols + N' )';
EXEC (@createstatement);
-- Populate #ParamPivot
SET @loopcount = 1;
WHILE @loopcount <= @combinations
BEGIN
SET @insertvalues = N'';
SET @insertstatement = N'INSERT INTO dbo.ParamPivot ( ' + @cols2 + N' ) VALUES(';
SET @paramcounter = 1;
WHILE @paramcounter <= @paramCount
BEGIN
SET @insertvalues = @insertvalues + N'@bit' + LTRIM(RTRIM(CAST(@paramcounter AS CHAR(100)))) + N', ';
SET @paramcounter = @paramcounter + 1;
END;
--Remove the trailing comma
SET @insertvalues = LEFT(@insertvalues, LEN(@insertvalues) - 1);
SET @insertvalues = @insertvalues + N')';
SET @fullstatement = @insertstatement + @insertvalues;
EXECUTE sp_executesql
@fullstatement,
@ParmDefinition,
@bit1,
@bit2,
@bit3,
@bit4,
@bit5,
@bit6,
@bit7,
@bit8,
@bit9,
@bit10,
@bit11,
@bit12,
@bit13,
@bit14,
@bit15,
@bit16,
@bit17,
@bit18,
@bit19,
@bit20;
SET @loopcount = @loopcount + 1;
END;
DECLARE
@updatestatement NVARCHAR(MAX) = '',
@updatefieldOutput NVARCHAR(100) = '',
@setfieldvarstatement NVARCHAR(MAX) = '',
@setfieldParmDefinition NVARCHAR(MAX) = '';
SET @setfieldParmDefinition
= N'@updatefield NVARCHAR(100) OUT,
@parameter1 NVARCHAR(100),
@parameter2 NVARCHAR(100),
@parameter3 NVARCHAR(100),
@parameter4 NVARCHAR(100),
@parameter5 NVARCHAR(100),
@parameter6 NVARCHAR(100),
@parameter7 NVARCHAR(100),
@parameter8 NVARCHAR(100),
@parameter9 NVARCHAR(100),
@parameter10 NVARCHAR(100),
@parameter11 NVARCHAR(100),
@parameter12 NVARCHAR(100),
@parameter13 NVARCHAR(100),
@parameter14 NVARCHAR(100),
@parameter15 NVARCHAR(100),
@parameter16 NVARCHAR(100),
@parameter17 NVARCHAR(100),
@parameter18 NVARCHAR(100),
@parameter19 NVARCHAR(100),
@parameter20 NVARCHAR(100) ';
-- Populate bit flags
DECLARE @divider INT = @combinations;
SET @loopcount = 1;
WHILE @divider > 1
BEGIN
SET @divider = @divider / 2;
SET @bitflipper = 1;
SET @setfieldvarstatement
= N'SELECT @updatefield = @parameter' + +LTRIM(RTRIM(CAST(@loopcount AS CHAR(100))));
EXECUTE sp_executesql
@setfieldvarstatement,
@setfieldParmDefinition,
@updatefieldOutput OUT,
@parameter1,
@parameter2,
@parameter3,
@parameter4,
@parameter5,
@parameter6,
@parameter7,
@parameter8,
@parameter9,
@parameter10,
@parameter11,
@parameter12,
@parameter13,
@parameter14,
@parameter15,
@parameter16,
@parameter17,
@parameter18,
@parameter19,
@parameter20;
-- This section will populate dbo.ParamPivot with
-- all the combinations of parameter use
DECLARE bitcursor CURSOR LOCAL FAST_FORWARD FOR SELECT id_num FROM dbo.ParamPivot;
OPEN bitcursor;
FETCH NEXT FROM bitcursor
INTO
@id;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @id % @divider = 1
OR @divider = 1
SET @bitflipper = ~ @bitflipper;
SET @updatestatement
= N'UPDATE p SET ' + @updatefieldOutput + N'= ' + LTRIM(RTRIM(CAST(@bitflipper AS CHAR(100))))
+ N' FROM dbo.ParamPivot p WHERE id_num = ' + LTRIM(RTRIM(CAST(@id AS CHAR(100))));
EXECUTE sp_executesql
@updatestatement;
FETCH NEXT FROM bitcursor
INTO
@id;
END;
CLOSE bitcursor;
DEALLOCATE bitcursor;
IF @loopcount = 1
BEGIN
EXECUTE sp_executesql
@updatestatement;
END;
SET @loopcount = @loopcount + 1;
END;
-- Demo query
--SELECT * FROM dbo.ParamPivot;
DECLARE
@fieldlist NVARCHAR(MAX),
@fieldquery NVARCHAR(MAX);
SET @fieldlist = N'';
SET @fieldquery = N'';
DECLARE
@paramlist NVARCHAR(MAX),
@sprocparamlist NVARCHAR(MAX),
@datatype NVARCHAR(100);
SET @paramlist = N'';
SET @sprocparamlist = N'';
SET @datatype = N'';
-----------------------------------------------------------------------------------------------------------------------------------------------
-- Build The Procedure
-----------------------------------------------------------------------------------------------------------------------------------------------
PRINT 'CREATE PROCEDURE [dbo].[RenameMePlease]';
SET @loopcount = 1;
WHILE @loopcount <= @paramCount
BEGIN
SELECT
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = @loopcount;
SET @paramlist = @paramlist + N'@param' + LTRIM(RTRIM(CAST(@loopcount AS CHAR(100)))) + N',';
SET @setfieldvarstatement
= N'SELECT @updatefield = @parameter' + LTRIM(RTRIM(CAST(@loopcount AS CHAR(100))));
--PRINT @setfieldvarstatement
EXECUTE sp_executesql
@setfieldvarstatement,
@setfieldParmDefinition,
@updatefieldOutput OUT,
@parameter1,
@parameter2,
@parameter3,
@parameter4,
@parameter5,
@parameter6,
@parameter7,
@parameter8,
@parameter9,
@parameter10,
@parameter11,
@parameter12,
@parameter13,
@parameter14,
@parameter15,
@parameter16,
@parameter17,
@parameter18,
@parameter19,
@parameter20;
SET @sprocparamlist = @sprocparamlist + CASE WHEN @loopcount =1 THEN '' ELSE + CHAR(10) END + N'@' + @updatefieldOutput + N' ' + @datatype + N' = NULL,';
SET @fieldlist = @fieldlist + @updatefieldOutput + N',';
SET @loopcount = @loopcount + 1;
END;
SET @sprocparamlist = LEFT(@sprocparamlist, LEN(@sprocparamlist) - 1);
PRINT @sprocparamlist;
PRINT 'AS';
PRINT '';
PRINT 'BEGIN';
SET @paramlist = LEFT(@paramlist, LEN(@paramlist) - 1);
DECLARE @cursorParmDefinition NVARCHAR(MAX);
SET @cursorParmDefinition
= N'@param1 NVARCHAR(100) OUT,
@param2 NVARCHAR(100) OUT,
@param3 NVARCHAR(100) OUT,
@param4 NVARCHAR(100) OUT,
@param5 NVARCHAR(100) OUT,
@param6 NVARCHAR(100) OUT,
@param7 NVARCHAR(100) OUT,
@param8 NVARCHAR(100) OUT,
@param9 NVARCHAR(100) OUT,
@param10 NVARCHAR(100) OUT,
@param11 NVARCHAR(100) OUT,
@param12 NVARCHAR(100) OUT,
@param13 NVARCHAR(100) OUT,
@param14 NVARCHAR(100) OUT,
@param15 NVARCHAR(100) OUT,
@param16 NVARCHAR(100) OUT,
@param17 NVARCHAR(100) OUT,
@param18 NVARCHAR(100) OUT,
@param19 NVARCHAR(100) OUT,
@param20 NVARCHAR(100) OUT';
SET @fieldlist = LEFT(@fieldlist, LEN(@fieldlist) - 1);
SET @fieldquery = N'DECLARE ParamPivotCursor CURSOR GLOBAL FAST_FORWARD
FOR SELECT ' + @fieldlist + N' FROM dbo.ParamPivot ORDER BY id_num';
EXECUTE sp_executesql
@fieldquery;
DECLARE @opencursor NVARCHAR(MAX);
SET @opencursor = N'OPEN ParamPivotCursor
FETCH NEXT FROM ParamPivotCursor
INTO ' + @paramlist;
EXECUTE sp_executesql
@opencursor,
@cursorParmDefinition,
@parameter1 OUT,
@parameter2 OUT,
@parameter3 OUT,
@parameter4 OUT,
@parameter5 OUT,
@parameter6 OUT,
@parameter7 OUT,
@parameter8 OUT,
@parameter9 OUT,
@parameter10 OUT,
@parameter11 OUT,
@parameter12 OUT,
@parameter13 OUT,
@parameter14 OUT,
@parameter15 OUT,
@parameter16 OUT,
@parameter17 OUT,
@parameter18 OUT,
@parameter19 OUT,
@parameter20 OUT;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @casecounter = @casecounter + 1;
SET @whereclause = N'WHERE ';
IF @query LIKE '%WHERE%'
BEGIN
SET @whereclause = N'AND ';
END;
SET @ifstatement = N'IF ';
IF @parameter1 = 1
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 1;
SET @whereclause = @whereclause + @fieldname + N' = ' + @param + N' AND ';
SET @ifstatement = @ifstatement + @param + N' IS NOT NULL AND ';
END;
IF @parameter1 = 0
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 1;
SET @ifstatement = @ifstatement + @param + N' IS NULL AND ';
END;
IF @parameter2 = 1
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 2
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 2;
SET @whereclause = @whereclause + @fieldname + N' = ' + @param + N' AND ';
SET @ifstatement = @ifstatement + @param + N' IS NOT NULL AND ';
END;
IF @parameter2 = 0
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 2
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 2;
SET @ifstatement = @ifstatement + @param + N' IS NULL AND ';
END;
IF @parameter3 = 1
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 3
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 3;
SET @whereclause = @whereclause + @fieldname + N' = ' + @param + N' AND ';
SET @ifstatement = @ifstatement + @param + N' IS NOT NULL AND ';
END;
IF @parameter3 = 0
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 3
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 3;
SET @ifstatement = @ifstatement + @param + N' IS NULL AND ';
END;
IF @parameter4 = 1
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 4
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 4;
SET @whereclause = @whereclause + @fieldname + N' = ' + @param + N' AND ';
SET @ifstatement = @ifstatement + @param + N' IS NOT NULL AND ';
END;
IF @parameter4 = 0
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 4
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 4;
SET @ifstatement = @ifstatement + @param + N' IS NULL AND ';
END;
IF @parameter5 = 1
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 5
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 5;
SET @whereclause = @whereclause + @fieldname + N' = ' + @param + N' AND ';
SET @ifstatement = @ifstatement + @param + N' IS NOT NULL AND ';
END;
IF @parameter5 = 0
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 5
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 5;
SET @ifstatement = @ifstatement + @param + N' IS NULL AND ';
END;
IF @parameter6 = 1
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 6
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 6;
SET @whereclause = @whereclause + @fieldname + N' = ' + @param + N' AND ';
SET @ifstatement = @ifstatement + @param + N' IS NOT NULL AND ';
END;
IF @parameter6 = 0
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 6
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 6;
SET @ifstatement = @ifstatement + @param + N' IS NULL AND ';
END;
IF @parameter7 = 1
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 7
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 7;
SET @whereclause = @whereclause + @fieldname + N' = ' + @param + N' AND ';
SET @ifstatement = @ifstatement + @param + N' IS NOT NULL AND ';
END;
IF @parameter7 = 0
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 7
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 7;
SET @ifstatement = @ifstatement + @param + N' IS NULL AND ';
END;
IF @parameter8 = 1
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 8
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 8;
SET @whereclause = @whereclause + @fieldname + N' = ' + @param + N' AND ';
SET @ifstatement = @ifstatement + @param + N' IS NOT NULL AND ';
END;
IF @parameter8 = 0
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 8
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 8;
SET @ifstatement = @ifstatement + @param + N' IS NULL AND ';
END;
IF @parameter9 = 1
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 9
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 9;
SET @whereclause = @whereclause + @fieldname + N' = ' + @param + N' AND ';
SET @ifstatement = @ifstatement + @param + N' IS NOT NULL AND ';
END;
IF @parameter9 = 0
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 9
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 9;
SET @ifstatement = @ifstatement + @param + N' IS NULL AND ';
END;
IF @parameter10 = 1
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 10
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 10;
SET @whereclause = @whereclause + @fieldname + N' = ' + @param + N' AND ';
SET @ifstatement = @ifstatement + @param + N' IS NOT NULL AND ';
END;
IF @parameter10 = 0
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 10
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 10;
SET @ifstatement = @ifstatement + @param + N' IS NULL AND ';
END;
IF @parameter11 = 1
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 11
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 11;
SET @whereclause = @whereclause + @fieldname + N' = ' + @param + N' AND ';
SET @ifstatement = @ifstatement + @param + N' IS NOT NULL AND ';
END;
IF @parameter11 = 0
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 11
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 11;
SET @ifstatement = @ifstatement + @param + N' IS NULL AND ';
END;
IF @parameter12 = 1
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 12
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 12;
SET @whereclause = @whereclause + @fieldname + N' = ' + @param + N' AND ';
SET @ifstatement = @ifstatement + @param + N' IS NOT NULL AND ';
END;
IF @parameter12 = 0
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 12
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 12;
SET @ifstatement = @ifstatement + @param + N' IS NULL AND ';
END;
IF @parameter13 = 1
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 13
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 13;
SET @whereclause = @whereclause + @fieldname + N' = ' + @param + N' AND ';
SET @ifstatement = @ifstatement + @param + N' IS NOT NULL AND ';
END;
IF @parameter13 = 0
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 13
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 13;
SET @ifstatement = @ifstatement + @param + N' IS NULL AND ';
END;
IF @parameter14 = 1
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 14
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 14;
SET @whereclause = @whereclause + @fieldname + N' = ' + @param + N' AND ';
SET @ifstatement = @ifstatement + @param + N' IS NOT NULL AND ';
END;
IF @parameter14 = 0
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 14
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 14;
SET @ifstatement = @ifstatement + @param + N' IS NULL AND ';
END;
IF @parameter15 = 1
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 15
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 15;
SET @whereclause = @whereclause + @fieldname + N' = ' + @param + N' AND ';
SET @ifstatement = @ifstatement + @param + N' IS NOT NULL AND ';
END;
IF @parameter15 = 0
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 15
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 15;
SET @ifstatement = @ifstatement + @param + N' IS NULL AND ';
END;
IF @parameter16 = 1
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 16
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 16;
SET @whereclause = @whereclause + @fieldname + N' = ' + @param + N' AND ';
SET @ifstatement = @ifstatement + @param + N' IS NOT NULL AND ';
END;
IF @parameter16 = 0
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 16
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 16;
SET @ifstatement = @ifstatement + @param + N' IS NULL AND ';
END;
IF @parameter17 = 1
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 17
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 17;
SET @whereclause = @whereclause + @fieldname + N' = ' + @param + N' AND ';
SET @ifstatement = @ifstatement + @param + N' IS NOT NULL AND ';
END;
IF @parameter17 = 0
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 17
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 17;
SET @ifstatement = @ifstatement + @param + N' IS NULL AND ';
END;
IF @parameter18 = 1
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 18
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 18;
SET @whereclause = @whereclause + @fieldname + N' = ' + @param + N' AND ';
SET @ifstatement = @ifstatement + @param + N' IS NOT NULL AND ';
END;
IF @parameter18 = 0
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 18
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 18;
SET @ifstatement = @ifstatement + @param + N' IS NULL AND ';
END;
IF @parameter19 = 1
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 19
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 19;
SET @whereclause = @whereclause + @fieldname + N' = ' + @param + N' AND ';
SET @ifstatement = @ifstatement + @param + N' IS NOT NULL AND ';
END;
IF @parameter19 = 0
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 19
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 19;
SET @ifstatement = @ifstatement + @param + N' IS NULL AND ';
END;
IF @parameter20 = 1
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 20
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 20;
SET @whereclause = @whereclause + @fieldname + N' = ' + @param + N' AND ';
SET @ifstatement = @ifstatement + @param + N' IS NOT NULL AND ';
END;
IF @parameter20 = 0
AND EXISTS
(
SELECT
*
FROM
dbo.tempParams
WHERE
rownum = 20
)
BEGIN
SELECT
@fieldname = param_alias,
@param = param,
@datatype = datatype
FROM
dbo.tempParams
WHERE
rownum = 20;
SET @ifstatement = @ifstatement + @param + N' IS NULL AND ';
END;
PRINT '--CASE ' + LTRIM(RTRIM(CAST(@casecounter AS CHAR(100))));
IF RIGHT(@ifstatement, 4) = 'AND '
SET @ifstatement = LEFT(@ifstatement, LEN(@ifstatement) - 4);
IF @ifstatement <> 'IF '
PRINT @ifstatement;
PRINT 'BEGIN';
PRINT @query;
IF @whereclause <> 'AND '
AND RIGHT(@whereclause, 4) = 'AND '
SET @whereclause = LEFT(@whereclause, LEN(@whereclause) - 4);
IF @whereclause <> 'WHERE '
AND @whereclause <> 'AND '
PRINT @whereclause;
PRINT 'END';
PRINT '';
DECLARE @fetchnext NVARCHAR(MAX);
SET @fetchnext = N'FETCH NEXT FROM ParamPivotCursor
INTO ' + @paramlist;
EXECUTE sp_executesql
@fetchnext,
@cursorParmDefinition,
@parameter1 OUT,
@parameter2 OUT,
@parameter3 OUT,
@parameter4 OUT,
@parameter5 OUT,
@parameter6 OUT,
@parameter7 OUT,
@parameter8 OUT,
@parameter9 OUT,
@parameter10 OUT,
@parameter11 OUT,
@parameter12 OUT,
@parameter13 OUT,
@parameter14 OUT,
@parameter15 OUT,
@parameter16 OUT,
@parameter17 OUT,
@parameter18 OUT,
@parameter19 OUT,
@parameter20 OUT;
END;
CLOSE ParamPivotCursor;
DEALLOCATE ParamPivotCursor;
PRINT 'END';
PRINT '';
PRINT 'GO';
END;
GO
