Behold The Power of Dynamic SQL- Part 1

November 26, 2018

This blog post has been adapted from my presentation at Pass Summit 2018. To download the powerpoint and scripts from that session, click here

I’m using Stack Overflow’s medium sized database 

It’s a bit painful to get in database form, please see Brent Ozar’s blog for instructions

See The Setup Objects

USE [StackOverflow2013]
GO

/****** Object:  UserDefinedTableType [dbo].[parameters]    Script Date: 10/20/2018 12:45:01 PM ******/
CREATE TYPE [dbo].[parameters] AS TABLE(
	[param] [nvarchar](100) NULL,
	[param_alias] [nvarchar](100) NULL,
	[datatype] [nvarchar](100) NULL
)
GO

CREATE TABLE [dbo].[tempParams](
	[param] [nvarchar](100) NULL,
	[fieldname] [nvarchar](100) NULL,
	[param_alias] [nvarchar](100) NULL,
	[whereclause] [nvarchar](200) NULL,
	[datatype] [nvarchar](100) NULL,
	[rownum] [bigint] NULL
) ON [PRIMARY]
GO
See The Indexes

USE [StackOverflow2013]
GO

SET ANSI_PADDING ON
GO

-------------------------------------------------------------------------------------
--dbo.Posts
-------------------------------------------------------------------------------------
/****** Object:  Index [IX_OwneruserId_Includes]    Script Date: 11/2/2018 7:09:02 PM ******/
CREATE NONCLUSTERED INDEX [IX_OwneruserId_Includes] ON [dbo].[Posts]
(
	[OwnerUserId] ASC
)
INCLUDE ( 	[Body],
	[CreationDate],
	[Score]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


-------------------------------------------------------------------------------------
--dbo.Users
-------------------------------------------------------------------------------------
/****** Object:  Index [IX_Age_Includes]    Script Date: 11/2/2018 7:09:26 PM ******/
CREATE NONCLUSTERED INDEX [IX_Age_Includes] ON [dbo].[Users]
(
	[Age] ASC
)
INCLUDE ( 	[DisplayName],
	[DownVotes],
	[Location],
	[Reputation],
	[UpVotes]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


/****** Object:  Index [IX_DisplayName_Includes]    Script Date: 11/2/2018 7:09:41 PM ******/
CREATE NONCLUSTERED INDEX [IX_DisplayName_Includes] ON [dbo].[Users]
(
	[DisplayName] ASC
)
INCLUDE ( 	[Age],
	[DownVotes],
	[Location],
	[Reputation],
	[UpVotes]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


/****** Object:  Index [IX_Location_Includes]    Script Date: 11/2/2018 7:09:52 PM ******/
CREATE NONCLUSTERED INDEX [IX_Location_Includes] ON [dbo].[Users]
(
	[Location] ASC
)
INCLUDE ( 	[Age],
	[DisplayName],
	[DownVotes],
	[Reputation],
	[UpVotes]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


-------------------------------------------------------------------------------------
--dbo.Votes
-------------------------------------------------------------------------------------
/****** Object:  Index [IX_CreationDate]    Script Date: 11/2/2018 7:10:07 PM ******/
CREATE NONCLUSTERED INDEX [IX_CreationDate] ON [dbo].[Votes]
(
	[CreationDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object:  Index [IX_PostId_Includes]    Script Date: 11/2/2018 7:10:17 PM ******/
CREATE NONCLUSTERED INDEX [IX_PostId_Includes] ON [dbo].[Votes]
(
	[PostId] ASC
)
INCLUDE ( 	[CreationDate],
	[VoteTypeId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

-------------------------------------------------------------------------------------
--dbo.VoteTypes
-------------------------------------------------------------------------------------
/****** Object:  Index [IX_Name]    Script Date: 11/2/2018 7:10:31 PM ******/
CREATE NONCLUSTERED INDEX [IX_Name] ON [dbo].[VoteTypes]
(
	[Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

At a previous job, we were not allowed to use Dynamic SQL.  At one point, a perfect use case for Dynamic SQL presented itself. The search procedure in question had around 15 nullable parameters and was incredibly slow. It looked something like this, but this one only has 10 nullable parameters.

See The BadSearchProc Code

USE [StackOverflow2013]
GO

/****** Object:  StoredProcedure [dbo].[BadSearchProc]    Script Date: 11/20/2018 8:12:03 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[BadSearchProc] 
@CreationDate datetime = null,
@postScore int = null,
@voteCreationDate datetime = null,
@votetype varchar(50) = null,
@askerDisplayName nvarchar(40) = null,
@askerLocation nvarchar(100) = null,
@askerUpVotes int = null,
@askerDownVotes int = null,
@askerRep int = null,
@askerAge int = null
AS

/*BadSearchProc*/
select 
  p.Id,
  p.Body,
  p.CreationDate,
  p.Score,
  v.CreationDate as voteCreationDate,
  vt.Name as voteType,
  asker.DisplayName as askerDisplayName,
  asker.Location as askerLocation,
  asker.UpVotes,
  asker.DownVotes,
  asker.Reputation,
  asker.Age
FROM dbo.Posts p
LEFT JOIN dbo.Votes v 
  ON p.Id = v.PostId
LEFT JOIN dbo.VoteTypes vt 
  on v.VoteTypeId = vt.Id
LEFT JOIN dbo.Users asker 
  ON p.OwnerUserId = asker.Id
where 
  (@CreationDate is null or p.CreationDate = @CreationDate)
and 
  (@postScore is null or p.Score = @postScore)
and 
  (@voteCreationDate is null or v.CreationDate = @voteCreationDate)
and 
  (@votetype is null OR vt.name = @votetype)
and 
  (@askerDisplayName is null or asker.DisplayName = @askerDisplayName)
and 
  (@askerLocation is null or asker.Location = @askerLocation)
and 
  (@askerUpVotes is null OR asker.UpVotes = @askerUpVotes)
and 
  (@askerDownVotes is null OR asker.DownVotes = @askerDownVotes)
and 
  (@askerRep is null or asker.Reputation = @askerRep)
and 
  (@askerAge is null or asker.Age = @askerAge);


GO

I wrote the stored procedure using Dynamic SQL, and ran a series of tests. I had three different scenarios that I compared:

  1. Old approach- no change
  2. Old approach with targeted indexing- no code changes
  3. New approach- Dynamic SQL

I presented my findings, and Dynamic SQL won hands down. Yes, targeted indexing can help, but a bad execution plan is a bad execution plan and indexes will not save you. Despite the strong case that I made, I would not be allowed to use Dynamic SQL to solve the performance problem.

One of the guys I presented to asked the question: “Is there anything you can do to replicate the results without using Dynamic SQL?”

Months later after I had turned in my resignation, and I came back to that question. I thought “Yes, there is something that I can do to achieve optimal execution plans without using Dynamic SQL”. I could already think of the downsides of this approach, but I decided to write a stored procedure that generates all of the cases anyways. The CASES (IF blocks) will give you a separate query that is tailored to fit the usage of the parameters at the time they are being used. Yes you could just use OPTION (RECOMPILE) but what’s the fun in that? And besides, I’m trying to avoid burning up the CPU. 

In this approach, some assumptions are being made. The stored procedure receives a query as one of the parameters.

  1. Everything in the query is necessary
  2. All of the JOINs in the query are necessary
  3. All of the fields in the SELECT clause are necessary
  4. The only thing that really matters here is the WHERE clause

I decided to name this new stored procedure useDynamicSQLInstead.

Here are some other potential names I’ve considered

  1. theUnmaintainableCodeGenerator
  2. theCustomerObfuscationTool

Here’s the code! I only coded it to work for 20 parameters. Doing it for any more than that is insane. In fact, I could only every get the procedure to finish for 17 parameters. 15 had over 450K lines of code!

See The useDynamicSQLInstead Proc
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

Within this stored procedure, you will find several interesting things

  • Dynamic SQL
    • Traditional use for SELECT and UPDATES
    • Dynamic Pivot
    • Output parameters
  • Acceptable Uses of Cursors (this proc will never live in a production environment!)
    • Local Fast_Forward
    • Global Fast_Forward

Using this procedure looks like this:

See the EXEC statement
USE [StackOverflow2013]
GO


DECLARE @param AS dbo.parameters

--To change the results, use more or less parameters
INSERT INTO @param
SELECT '@CreationDate','p.CreationDate','DATETIME'
UNION ALL
SELECT '@postScore','p.Score','INT'
UNION ALL
SELECT '@voteCreationDate','v.CreationDate','DATETIME'
UNION ALL
SELECT '@votetype','vt.name','VARCHAR(50)'
UNION ALL
SELECT '@askerDisplayName','asker.DisplayName','NVARCHAR(40)'
UNION ALL
SELECT '@askerLocation','asker.Location','NVARCHAR(100)'
UNION ALL
SELECT '@askerUpVotes','asker.UpVotes','INT'
UNION ALL
SELECT '@askerDownVotes','asker.DownVotes','INT'
UNION ALL
SELECT '@askerRep','asker.Reputation','INT'
UNION ALL
SELECT '@askerAge','asker.Age','INT'


-- This may take awhile if you have a high number of parameters
EXEC dbo.useDynamicSQLinstead 
@query = N'select 
  p.Id,
  p.Body,
  p.CreationDate,
  p.Score,
  v.CreationDate as voteCreationDate,
  vt.Name as voteType,
  asker.DisplayName as askerDisplayName,
  asker.Location as askerLocation,
  asker.UpVotes,
  asker.DownVotes,
  asker.Reputation,
  asker.Age
FROM dbo.Posts p
LEFT JOIN dbo.Votes v 
  ON p.Id = v.PostId
LEFT JOIN dbo.VoteTypes vt 
  on v.VoteTypeId = vt.Id
LEFT JOIN dbo.Users asker 
  ON p.OwnerUserId = asker.Id',
@parameters = @param

If you want to see the full stored procedure that is generated, please download the files or run the EXEC yourself.  As you can see, there are 1024 cases, and the proc is 26,641 lines long.

 

Take the output, paste it into another window, remove the (10 rows affected) message at the top, rename the stored proc to “MonsterSearchProc” and click Execute. If you scroll all the way to the bottom, it should look like this:

Why so many cases? Our refactor for a relatively small search procedure turned into a gigantic mess! An UNMAINTAINABLE collection of very precise queries surrounded with IF statements evaluating whether each of the parameters was being used or if it was NULL. Long story short- ORs are bad! They might seem like something you’re familiar with, but the truth is, they cause an awful lot of complexity to enter the execution plans.

Each optional parameter has 2 ways it can be used: It can be NULL, or it can be populated.
For every parameter that is in play in your query, you multiply the possibilities together.
This gets us to Powers of 2.

One parameter leads you to 2 different queries (CASES) 2^1
Two parameters leads you to 4 different queries (CASES) 2^2
.
.
Ten parameters leads you to 1,024 different queries (CASES) 2^10
.
.
Twenty parameters leads you to 1,048,576 different queries (CASES) 2^20

Without OPTION (RECOMPILE), the execution plan will end up “sniffing” the parameters and it will sniff all NULLS. NULLS mean that you don’t really want to filter, so you need to SCAN the entire table just to be safe. If you did want to filter, you could use a SEEK which is much less expensive in terms of READS and is consequently much faster.

Our MonsterSearchProc, like the Dynamic SQL approach, will lead us to better execution plans. Indexes will be better utilized, and searching will be much faster.

In case it isn’t clear, there are several reasons to use Dynamic SQL.

  • Easier to maintain that writing all of the queries, although Dynamic SQL can end up being quite complex
  • Dynamic SQL can do things that normal SQL just can’t do, like PIVOT with dynamic column names
  • You will only enter the proc cache as you need a plan for that particular combination of parameter use. The MonsterSearchProc definitely bloats the cache, and bloat will make your DBAs unhappy!

Finally, here’s the BadSearchProc refactored using Dynamic SQL

USE [StackOverflow2013]
GO

/****** Object:  StoredProcedure [dbo].[GoodSearchProc]    Script Date: 11/20/2018 10:40:49 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO






CREATE PROCEDURE [dbo].[GoodSearchProc] 
@CreationDate datetime = null,
@postScore int = null,
@voteCreationDate datetime = null,
@votetype varchar(50) = null,
@askerDisplayName nvarchar(40) = null,
@askerLocation nvarchar(100) = null,
@askerUpVotes int = null,
@askerDownVotes int = null,
@askerRep int = null,
@askerAge int = null,
@debug int = 0
AS

declare @SQL nvarchar(max) = ''
declare @ParameterDefinitions nvarchar(max) = ''

-- We are assuming that all of the fields in the fields list 
-- are necessary, and all of the JOINs are necessary
set @SQL = '/*Good*/ 
  select 
  p.Id,
  p.Body,
  p.CreationDate,
  p.Score,
  v.CreationDate as voteCreationDate,
  vt.Name as voteType,
  asker.DisplayName as askerDisplayName,
  asker.Location as askerLocation,
  asker.UpVotes,
  asker.DownVotes,
  asker.Reputation,
  asker.Age
  FROM dbo.Posts p
  LEFT JOIN dbo.Votes v 
    ON p.Id = v.PostId
  LEFT JOIN dbo.VoteTypes vt 
    on v.VoteTypeId = vt.Id
  LEFT JOIN dbo.Users asker 
    ON p.OwnerUserId = asker.Id '

-- If at least one filter is used, add the WHERE clause
IF @CreationDate IS NOT NULL 
  OR @postScore IS NOT NULL
  OR @voteCreationDate IS NOT NULL
  OR @voteType IS NOT NULL 
  OR @askerDisplayName IS NOT NULL
  OR @askerLocation IS NOT NULL
  OR @askerUpVotes IS NOT NULL
  OR @askerDownVotes IS NOT NULL 
  OR @askerRep IS NOT NULL 
  OR @askerAge IS NOT NULL
BEGIN
  set @SQL = @SQL + '
  WHERE '
END


IF @CreationDate IS NOT NULL
BEGIN
  set @SQL = @SQL + '
    p.CreationDate = @CreationDate AND ';
END  

IF @postScore IS NOT NULL
BEGIN
  set @SQL = @SQL + '
    p.Score = @postScore AND ';
END

IF @voteCreationDate IS NOT NULL
BEGIN
  set @SQL = @SQL + '
    v.CreationDate = @voteCreationDate AND ';
END

IF @votetype IS NOT NULL
BEGIN
  set @SQL = @SQL + '
    vt.name = @votetype AND ';
END

if @askerDisplayName IS NOT NULL
BEGIN
  set @SQL = @SQL + '
    asker.DisplayName = @askerDisplayName AND ';
END

IF @askerLocation IS NOT NULL
BEGIN
  set @SQL = @SQL + '
    asker.Location = @askerLocation AND ';
END

IF @askerUpVotes IS NOT NULL
BEGIN
  set @SQL = @SQL + '
    asker.UpVotes = @askerUpVotes AND ';
END

IF @askerDownVotes IS NOT NULL
BEGIN
  set @SQL = @SQL + '
    asker.DownVotes = @askerDownVotes AND '
END

IF @askerRep IS NOT NULL
BEGIN
  set @SQL = @SQL + '
    asker.Reputation = @askerRep AND ';
END

IF @askerAge IS NOT NULL
BEGIN
  set @SQL = @SQL + '
    asker.Age = @askerAge AND ';
END

--Remove the trailing 'AND '
set @SQL = LEFT(@SQL, LEN(@SQL) - 4)

set @ParameterDefinitions = 
  '@CreationDate datetime,
  @postScore int,
  @voteCreationDate datetime,
  @votetype varchar(50),
  @askerDisplayName nvarchar(40),
  @askerLocation nvarchar(100),
  @askerUpVotes int,
  @askerDownVotes int,
  @askerRep int,
  @askerAge int'

if @debug = 1
BEGIN
  PRINT @SQL;
END

--Execute Dynamic SQL
EXECUTE sp_executesql
  @SQL,                       --Dynamic SQL string
  @ParameterDefinitions,      --Parameter definitions
  @CreationDate,              --Parameter1
  @postScore,                 --Parameter2
  @voteCreationDate,          --Parameter3
  @votetype,                  --Parameter4
  @askerDisplayName,          --Parameter5
  @askerLocation,             --Parameter6
  @askerUpVotes,              --Parameter7
  @askerDownVotes,            --Parameter8
  @askerRep,                  --Parameter9
  @askerAge                   --Parameter10



GO

In my next post, I will go over what is happening in the execution plans for the BadSearchProc, MonsterSearchProc, and GoodSearchProc.

Thanks for reading!

Related Posts

Dynamic SQL- Intro

November 5, 2017

SQL Server Missteps

February 2, 2016

T-SQL Merge Statements- Just Don’t

March 16, 2021