Stored Procedure with Variable Number of Parameters

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

Creating a stored procedure with an indefinite number of parameters

The following code demonstrates a means of handling optional parameters in a stored procedure. All of the possible parameters must be known in advance, but the SP can be called with a subset of parameters.

create procedure AddUser
  @UserId Int Output,
  @FirstName NVarChar(64),
  @LastName NVarChar(64),
  @FavoriteHandbag NVarChar(64) = NULL,
  @FavoriteShoe NVarChar(64) = NULL,
  @FavoriteWeapon NVarChar(64) = NULL
as
  -- Validate the inputs.
  if ( @FavoriteHandbag is NULL ) and ( @FavoriteShoe is NULL ) and ( @FavoriteWeapon is NULL )
    begin
    RaIsError( 'New users must have at least one favorite specified.', 13, 0 );
    return;
    end
  -- tbd: Check for missing or duplicate name, ... .

  -- Store the data.
  insert into Users ( FirstName, LastName, FavoriteHandbag, FavoriteShoe, FavoriteWeapon )
    values ( @FirstName, @LastName, @FavoriteHandbag, @FavoriteShoe, @FavoriteWeapon );

  -- Return the new user's   UserId .
  set @UserId = Scope_Identity();
go

-- Test the SP.
declare @UserId as Int;

-- Without any favorites it ought to fail.
exec AddUser @UserId = @UserId output, @FirstName = 'William', @LastName = 'Shakespeare';

-- With any combination of favorites it ought to succeed.
exec AddUser @UserId = @UserId output, @FirstName = 'William', @LastName = 'Shakespeare',
  @FavoriteWeapon = 'pen';

exec AddUser @UserId = @UserId output, @FirstName = 'William', @LastName = 'Shakespeare',
  @FavoriteShoe = 'moccasin', @FavoriteWeapon = 'pen';

If the Real Problem™ is that the parameters may contain an arbitrary list of name/value pairs, e.g. { 'Topping', 'Whipped Cream' }, that are unknown in advance then a different method must be used. If the number of pairs is limited then parameters could be used, e.g. @Name1, @Value1, @Name2, @Value2, ... . A TVP or XML parameter would provide a tidier representation of a list of pairs.

Variable number of parameters in a Firebird stored procedure

Firebird does not support a variable number of parameters to a stored procedure. However, you can define default parameter values. So, you could specify a first parameter without default, followed by multiple parameters with a default, and then call the stored procedure with 1 or more parameters.

create procedure SP_STOCK_ANALYSIS (
    group_1 CHAR(6), group_2 CHAR(6) DEFAULT NULL, group_3 CHAR(6) DEFAULT NULL /* ... etc ...*/)
  RETURNS (
    STOCK_CODE CHAR(21),
    STOCK_GROUP CHAR(6),
    DESCRIPTION CHAR(31),
    EXPENSE NUMERIC(15, 4))
as
begin
  for select L.STOCK_CODE /* ... etc ... */
    from LEDGER L LEFT JOIN INVENTORY INV ON L.STOCK_CODE = INV.STOCK_CODE
    where INV.STOCK_GROUP in (group_1, group_2, group_3 /* ... etc ... */)
    /* ... etc ... */
    into STOCK_CODE /* ... etc ... */
  do
  begin
    /* ... etc ... */
  end
end

Alternatively, you could pass a comma separated string, and use a helper stored procedure to split that string into multiple rows.

You would then do something like

create procedure SP_STOCK_ANALYSIS(group_list VARCHAR(8191)
  RETURNS (
    STOCK_CODE CHAR(21),
    STOCK_GROUP CHAR(6),
    DESCRIPTION CHAR(31),
    EXPENSE NUMERIC(15, 4))
as
begin
  for select L.STOCK_CODE /* ... etc ... */
    from LEDGER L LEFT JOIN INVENTORY INV ON L.STOCK_CODE = INV.STOCK_CODE
    where INV.STOCK_GROUP in (select group_value from split_groups(:group_list))
    /* ... etc ... */
    into STOCK_CODE /* ... etc ... */
  do
  begin
    /* ... etc ... */
  end
end

With split_groups something like

create procedure split_group(group_list varchar(8191))
  returns (group_value varchar(1000))
as
  declare previouspos smallint = 1;
  declare nextpos smallint;
begin
  -- produce no rows for null input
  if (group_list is null) then exit;
  -- find next , in group_list
  nextpos = position(',', group_list);
  while (nextpos <> 0) do
  begin
    -- get item
    group_value = substring(group_list from previouspos for nextpos - previouspos);
    if (char_length(group_value) > 0) then
      -- output item as a row
      suspend;
    -- first character after the found ,
    previouspos = nextpos + 1;
    -- find next , in group_list
    nextpos = position(',', group_list, previouspos);
  end
  -- output item after last found ,
  group_value = substring(group_list from previouspos);
  if (char_length(group_value) > 0) then
    suspend;
end

Creating Stored Procedure With Variable Number of Parameters

Put them in an XML and try OPENXML feature.

http://msdn.microsoft.com/en-us/library/ms186918.aspx

Oracle Stored Procedure with variable number of IN parameters of the same type

If you want to pass in a list of parameters, you should really use a collection rather than passing in multiple parameters.

CREATE TYPE id_arr 
    AS TABLE OF VARCHAR2(100);

create or replace PROCEDURE QueryMultipleValues (
  p_ids IN id_arr
)

If you want your procedure to accept a variable number of parameters, assuming that you can determine the maximum number of parameters you want to accept in advance, you can define a procedure with optional parameters

create or replace PROCEDURE QueryMultipleValues (
  p_id1 IN VARCHAR2,
  p_id2 IN VARCHAR2 = null,
  p_id3 IN VARCHAR2 = null,
  ..
  p_idN IN VARCHAR2 = null,
)

can be called with between 1 and N parameters.

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'

Stored procedure that creates a table with dynamic number of parameters

The parameters need to be defined in the Stored Proc.

To pass a variable numbers of parms, you can pass the parameters as a delimited list in a string, so you are passing a single string parameter, then the stored procedure can split the string and loop through each element of the array.

MYVAR = 'A~B~C';

CREATE PROCEDURE PROCABC(MYVAR VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
var ARRAY_STR = MYVAR.split("~");
//process ARRAY_STR in a loop

Update a variable number of parameters in a Stored Procedure

Yes, you can use COALESCE to do this. Basically if the parameter is passed in as NULL then it will use the original value. Below is a general pattern that you can adapt.

DECLARE @LastName NVARCHAR(50) = 'John'
DECLARE @FirstName NVARCHAR(50) = NULL;
DECLARE @ID INT = 1;

UPDATE dbo.UpdateExample
SET LastName = COALESCE(@LastName, LastName), FirstName = COALESCE(@FirstName, FirstName), 
WHERE ID = @ID

Also, have a read of this article, titled: The Impact of Non-Updating Updates

http://web.archive.org/web/20180406220621/http://sqlblog.com:80/blogs/paul_white/archive/2010/08/11/the_2D00_impact_2D00_of_2D00_update_2D00_statements_2D00_that_2D00_don_2D00_t_2D00_change_2D00_data.aspx

Basically,
"SQL Server contains a number of optimisations to avoid unnecessary logging or page flushing when processing an UPDATE operation that will not result in any change to the persistent database."

---

Related Topics