How to Avoid Dynamic SQL When Using an Undetermined Number of Parameters

How do I avoid dynamic SQL when using an undetermined number of parameters?

For an extensive overview concerning this and similar problems see: http://www.sommarskog.se/dyn-search-2005.html

Specific to your question is the part here: http://www.sommarskog.se/dyn-search-2005.html#AND_ISNOTNULL

Also take into account that a (straight) dynamic Solution is not necessarily slower than a (possibly convoluted) static one, as query plans can still get cached: see http://www.sommarskog.se/dyn-search-2005.html#dynsql

So you'll have to carefully test/measure your options against realistic amounts of data, taking into account realistic queries (e.g. searches with one or two parameters might be way more common than searches with ten, etc.)

---

EDIT: Questioner gave a good reason to optimize this in the comments, hence moving the 'premature' warning a bit out of the way:

The (standard ;) word of warning applies, though: This smells a lot like premature optimization! - Are you sure this sproc will get called that often that using dynamic SQL will be significantly slower (that is, compared to other stuff going on in your app)?

How to avoid inline SQL when using LIKE clause with variable number of parameters

Put your search terms in a table and join them:

declare @SSN table (SSN char(9))
insert into @SSN select '123456789' union select '456789321' union select '789123456'

declare @SearchString table (SearchString varchar(9))
insert into @SearchString select '893' union select '9123'

select ssn.SSN
from @SSN ssn
join @SearchString sst on ssn.SSN like '%' + sst.SearchString +'%'

If performance is an issue, and if your search strings are always at least 5 characters long (for example), you could create a table with each SSN and every possible 5-character substring. Then you could join the columns on matching values instead of partial values. But again, that depends on your requirements.

Pass in Dynamic number of parameters to a stored procedure

You can pass in a comma seperated list, use a table function to split that out into a table and then use an IN clause. This article goes over doing that.

table function:

CREATE FUNCTION dbo.funcListToTableInt(@list as varchar(8000), @delim as varchar(10))
RETURNS @listTable table(
  Value INT
  )
AS
BEGIN
    --Declare helper to identify the position of the delim
    DECLARE @DelimPosition INT

    --Prime the loop, with an initial check for the delim
    SET @DelimPosition = CHARINDEX(@delim, @list)

    --Loop through, until we no longer find the delimiter
    WHILE @DelimPosition > 0
    BEGIN
        --Add the item to the table
        INSERT INTO @listTable(Value)
            VALUES(CAST(RTRIM(LEFT(@list, @DelimPosition - 1)) AS INT))

        --Remove the entry from the List
        SET @list = right(@list, len(@list) - @DelimPosition)

        --Perform position comparison
        SET @DelimPosition = CHARINDEX(@delim, @list)
    END

    --If we still have an entry, add it to the list
    IF len(@list) > 0
        insert into @listTable(Value)
        values(CAST(RTRIM(@list) AS INT))

  RETURN
END
GO

Then your stored proc can do this:

SELECT *
FROM tbl 
WHERE id IN (
            SELECT Value
            FROM funcListToTableInt(@ids,',')
                   )

T-SQL - stored procedure that would handle an unknown number of parameters of unknown types

You can try to use XML based approach for your problem. Send an XML as an input parameter. Convert the xml into a table. From the table form dynamic query and execute it in order to achieve your desired functionality. See the below example. In this example Departments is a table which contains 3 columns pkDepartmentId (int), DepartmentName (varchar) and BuildingNumber (int). Using this approach you can send n Number of parameters and their values as input parameters.

pkDepartmentId DepartmentName BuildingNumber
1 Electronics and Communication 1
2 Computer Science 2
3 Instrumentation and Technology 4

--EXEC TestProc '<Parameters>
--                  <Param>
--                      <ColumnName>pkDepartmentId</ColumnName>
--                      <ColumnValue>1</ColumnValue>        
--                  </Param>
--                  <Param>
--                      <ColumnName>DepartmentName</ColumnName>
--                      <ColumnValue>Electronics and Communication</ColumnValue>        
--                  </Param>
--                  <Param>
--                      <ColumnName>BuildingNumber</ColumnName>
--                      <ColumnValue>1</ColumnValue>        
--                  </Param>    
--              </Parameters>'

CREATE PROCEDURE TestProc
@parameters XML
AS
BEGIN
    DECLARE @temp1 TABLE
    (
          ColName VARCHAR(100)
        , ColVal VARCHAR(4000)      
    )

    INSERT INTO @temp1
    SELECT    Params.Col.value('ColumnName[1]', 'VARCHAR(50)') ColName
            , Params.Col.value('ColumnValue[1]', 'VARCHAR(50)') ColVal              FROM   @parameters.nodes('//Parameters/Param') Params(Col)

    DECLARE @sql VARCHAR(4000)

    SET @sql = 'SELECT * FROM Departments WHERE '

    SELECT @sql = @sql  + ColName + ' = ''' + ColVal  + ''' AND '
    FROM @temp1

    -- Trim last AND
    SET @sql = SUBSTRING(@sql, 1, LEN(@sql) - 3)

    PRINT @sql                  

    EXEC (@sql)             

END

On executing this proc following result is obtained
pkDepartmentId DepartmentName BuildingNumber
1 Electronics and Communication 1

SQL Server - filter query by variable number of parameters

CREATE PROCEDURE [dbo].[p_Search] ( @Name sysname = NULL, @Objectid INT  = NULL, @schemaId INT  = NULL )
AS
BEGIN
    SELECT
        [name]
       ,[object_id]
       ,[principal_id]
       ,[schema_id]
       ,[parent_object_id]
       ,[type]
       ,[type_desc]
       ,[create_date]
       ,[modify_date]
       ,[is_ms_shipped]
       ,[is_published]
       ,[is_schema_published]
    FROM
        [sys].[objects]
    WHERE 1                                = 1
          AND [name]                         = ISNULL(@Name, [name])
          AND ISNULL(@Objectid, [object_id]) = [object_id]
          AND ISNULL(@schemaId, [schema_id]) = [schema_id];
END;

EXEC p_search @Name = 'sysallocunits'

Using variables within a dynamic sql script

You can pass through variables to dynamic SQL via sp_executesql

• Note that you should always use QUOTENAME to escape object names
• Also, dynamic SQL variables should always be nvarchar
• You also should not use variable coalescing to aggregate, instead use STRING_AGG or FOR XML

DECLARE 
    @sColumns               AS NVARCHAR(MAX) = '',
    @sAlterTableDynamicSQL  AS NVARCHAR(MAX),
    @sGUID                  AS VARCHAR(MAX) = CAST(NEWID() AS VARCHAR(MAX))

            
SET @sAlterTableDynamicSQL =
'
SELECT @sColumns = STRING_AGG(CAST([name] AS nvarchar(max)), N'','')
FROM Tempdb.sys.columns
WHERE [object_id] = object_id(N''tempdb..' + QUOTENAME(@sNomTableTemporaire, '''') + ''');
';

PRINT (@sAlterTableDynamicSQL);

EXEC sp_executesql
  @sAlterTableDynamicSQL,
  N'@sColumns nvarchar(max) OUTPUT'
  @sColumns = @sColumns OUTPUT;

But you don't actually need dynamic SQL here at all. You can pass the table name straight to object_id()

DECLARE 
    @sColumns               AS NVARCHAR(MAX) = '',
    @sAlterTableDynamicSQL  AS NVARCHAR(MAX),
    @sGUID                  AS VARCHAR(MAX) = CAST(NEWID() AS VARCHAR(MAX))
            
SELECT @sColumns = STRING_AGG(CAST([name] AS nvarchar(max)), N',')
FROM Tempdb.sys.columns
WHERE [object_id] = object_id(N'tempdb..' + QUOTENAME(@sNomTableTemporaire));

For SQL Server 2016 and earlier, you can use the FOR XML PATH('') method

Stored procedure with variable number of parameters

You could pass it in as a comma-separated list, then use a split function, and join against the results.

CREATE FUNCTION dbo.SplitInts
(
   @List       VARCHAR(MAX),
   @Delimiter  CHAR(1)
)
RETURNS TABLE
AS
   RETURN 
   (
       SELECT Item = CONVERT(INT, Item)
       FROM
       (
           SELECT Item = x.i.value('(./text())[1]', 'INT')
           FROM
           (
               SELECT [XML] = CONVERT(XML, '<i>' 
                    + REPLACE(@List, @Delimiter, '</i><i>') 
                    + '</i>').query('.')
           ) AS a
           CROSS APPLY
           [XML].nodes('i') AS x(i)
       ) AS y
       WHERE Item IS NOT NULL
   );

Now your stored procedure:

CREATE PROCEDURE dbo.doStuff
    @List VARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT cols FROM dbo.table AS t
        INNER JOIN dbo.SplitInts(@List, ',') AS list
        ON t.ID = list.Item;
END
GO

Then to call it:

EXEC dbo.doStuff @List = '1, 2, 3, ...';

You can see some background, other options, and performance comparisons here:

• Split strings the right way – or the next best way
• Splitting Strings : A Follow-Up
• Splitting Strings : Now with less T-SQL
• Comparing string splitting / concatenation methods
• Processing a list of integers : my approach
• Splitting a list of integers : another roundup
• More on splitting lists : custom delimiters, preventing duplicates, and maintaining order
• Removing Duplicates from Strings in SQL Server

On SQL Server 2016 or above, though, you should look at STRING_SPLIT() and STRING_AGG():

• Performance Surprises and Assumptions : STRING_SPLIT()
• STRING_SPLIT() in SQL Server 2016 : Follow-Up #1
• STRING_SPLIT() in SQL Server 2016 : Follow-Up #2
• SQL Server v.Next : STRING_AGG() performance
• Solve old problems with SQL Server’s new STRING_AGG and STRING_SPLIT functions

---

Related Topics