Learning SQL – Cyndi Johnson

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:

Old approach- no change
Old approach with targeted indexing- no code changes
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.

Everything in the query is necessary
All of the JOINs in the query are necessary
All of the fields in the SELECT clause are necessary
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

theUnmaintainableCodeGenerator
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!

Let me start by saying that I am NOT a DBA, by choice. I am a developer. I am a SQL Server developer with experience in application development. I have worked with databases for almost 10 years. I only briefly worked with ACTUAL DBAs when I worked in the healthcare industry, which only lasted 9 months. I have worked with several senior developers who were assigned DBA duties.Being near databases you pick up things. You observe and get a general gist of what an actual DBA would do all day. I think for a brief second I aspired to be a DBA- (that’s where the money is, right?) but quickly saw the error of my ways. I really have very little interest in being away from the code.

At my day job, I do all things IT. I support an accounting ERP that is on SQL Server 2008r2. I use TSQL, SSIS, and SSRS mainly. Since I am literally ALL THINGS IT, I am the de facto DBA. Brent Ozar was right! All I had to do was be a developer and stand near a database! Brent Ozar’s Advice

Great, so now that those three letters are officially on my resume, people want me to do actual Database Administrator things. This is fine at my day job, because the data is relatively small. My two biggest tables, which are archive tables, hold 2 and 3 million records. The company I’m at now is also really good about spending money on IT, so my SQL Server box is pretty freaking fabulous. It runs on VMware, and has plenty of RAM and space dedicated to it. If I wanted to reindex all of the tables in the application, it might take 15 minutes. But not everyone is as lucky as I am.

I also freelance. My main freelance client runs the same software that I support, also in a 2008r2 environment. However, they are a much bigger company than where I’m at now. If I were to compare sizes, I’d guess they’re 10 times bigger. Most of the time, they only ask me to code thank goodness, but occasionally they’ve asked me to do some DBA things.

Enough background. Here are my top 4 most painful moments involving SQL Server. Things I wish someone had told me years ago, but I’m not sure these lessons would be burned into me like they are now.

Forgetting the WHERE Clause– No big deal if you’re running a SELECT statement. It’s taking too long? Just hit the cancel button. On an UPDATE though? Yeah, be sure to wrap that inside of a BEGIN TRANSACTION. I ran an UPDATE where I was supposed to change a transaction counter inside ONE record in a table with only maybe 100 records, but by missing the WHERE clause, I changed ALL of the transaction numbers in their entire accounting system TO THE SAME NUMBER. I had to call them, and ask nicely but in an urgent voice “You need to get all of the users out of the system RIGHT NOW!” I knew full well what the application would do. It would begin throwing errors- not real errors, just messages- that it can’t get a transaction number and you can’t save your transaction. No Bueno. Users would begin freaking out and all hell would break lose. Luckily, I had an awesome coworker at the time who knew the data so well that we quickly fixed the bad transaction numbers and everyone was able to resume work. Phew. That was stressful. Moral of the story? Use the ‘BEGIN TRANSACTION/ROLLBACK/COMMIT’ commands
Disabling All Indexes on a Table- This was my freelance client, which was the same client affected by #1. Once a month, they’d have me perform the “archive” process, which would move records from the current tables to the history tables, but it was done through the UI. I’d only archive one or two months at a time, but gradually this process ended up taking all night. I researched indexes, and inserts/deletes, and learned that you can greatly speed up the INSERT process by disabling the indexes. So one night I decided to try a new approach- I disabled the indexes on the main table, which was roughly 30 MILLION rows, and then ran the archive process. It was incredibly fast! What took 7 hours now took 5 minutes! I had disabled the indexes through the UI in SSMS, so I thought I could just re-enable them the same way. That didn’t work. The UI flaked out on me. Luckily though, I could tell the indexes weren’t back. I quickly figured out how to reindex the entire table using TSQL commands, but the process took all night. I had no idea how long it would take, so it was a very stressful night indeed. I really wish there was a decent way to calculate how long it’s going to take to reindex-WITHOUT having to TEST it! Who has a spare SQL Server configured EXACTLY as their production server? I don’t. Next time, I think I’d reindex ONE index and time it, and then do some sort of wild projection. Moral of the story? Disabling indexes makes INSERTS go faster, but rebuilding the indexes may take time! Being on Standard Edition SUCKS.
Do NOT Do This In Production! A little more background- since I’m the DBA and all things IT, and I work for a smallish company, I have no need for a “test” SQL Server. I still have a test environment, but those databases are on my production server. So when a friend of mine who works for a much bigger company suggested that I run DBCC FREEPROCCACHE, he didn’t realize the kind of power I wield. When the command took a little longer than I was thinking it would, I freaked out and cancelled it while it was beginning to occur to me that this might be a SERVER specific command, and not a DATABASE specific one. “Don’t do that in production” he says, afterwards. Ooops. It pretty much wiped out my entire plan cache. I’m sure the system slowed down, but no one said anything. Sometimes it’s a good thing I’m the only one at my company capable of understanding the mistakes I make. Glenn Berry, you are a wise man. Wish I had read this first: Glenn Berry- Fun With DBCC FREEPROCCACHE Moral of the story? Some DBCC commands may be database specific, but others are SERVER specific. Know what you’re doing BEFORE you hit EXECUTE!
Don’t get married to BEGIN TRANSACTION! After I forgot the WHERE clause, I used BEGIN TRANSACTION everywhere. Most of the time, it’s fine. Except for that one time I was trying to DELETE a ton of records from a very big table, before a major server upgrade. That night was full of mistakes, but at least I suggested that a full backup be taken prior to the migration. I should’ve INSISTED. Anyways, it was asked of me to DELETE a bunch of records, but the fact that I only had so long to do it wasn’t clearly communicated to me. I had wrapped the DELETE statement inside of a BEGIN TRANSACTION, not realizing that when you do that SQL Server logs things differently so it can roll everything back. The DELETE was taking forever. Who knows how long it would’ve taken, because I had to CANCEL it. And then I crashed SSMS because it wouldn’t close. That threw the database into RECOVERING state, which meant no server migration could happen until that ended. That was a very stressful night for me and the IT manager of that company. I called Microsoft on that one, hoping there was something we could do to get it out of that state sooner. Nope, they said. Just let it finish. Eventually it finished, and by some miracle, the server migration finished on time too. Luckily, Microsoft didn’t bill me. Moral of the story? You don’t need to ROLLBACK everything! Use BEGIN TRANSACTION wisely.

T-SQL Merge Statements- Just Don’t

Behold The Power of Dynamic SQL- Part 1

Dynamic SQL- When To Use?

Dynamic SQL- Intro

SQL Server Missteps