Search code examples
sql-servertable-valued-parameters

Can a stored proc be declared with a table-valued parameter along with other parameters in SQL Server?


Edit: one of the comments mentions "misuse" of parentheses around the parameter. I'm attaching a screen-capture of the Microsoft documentation page showing those parentheses; and my stored proc wouldn't compile without them.

parentheses around table-valued parameter in stored proc

I am trying to use a table-valued parameter with a stored procedure in SQL Server 2017 but in conjunction with some other parameters; however that mixing of parameter types doesn't seem to be supported when a table-valued parameter is involved, though that limitation is not listed in the Limitations of Table-Valued Parameters section of the documentation. Can someone please confirm whether that is indeed a limitation or if I am making a mistake?

  create type IntegerList as TABLE (i int);

  -- this is invalid syntax:
  create proc Test
   @foo char(1),
   @idlist dbo.IntegerList READONLY
   as
   begin
   <snip>
   end

The table-valued parameter must be surrounded in parentheses:

  -- this succeeds
  create proc Test
  (@idlist dbo.IntegerList READONLY)
  as
  begin
  <snip>
  end

but if I try to insert a parameter before or after the table-valued parameter there's a syntax error:

  -- this fails with syntax error
  create proc Test
   @foo char(1),
  (@idlist dbo.IntegerList READONLY)
   as
   begin
   <snip>
   end

Solution

  • You sure can, here's a working example:

    CREATE TYPE dbo.IntList AS TABLE (Ints INT);
    
    GO
    
    CREATE PROC dbo.TestProc @Int INT, @intList dbo.IntList READONLY
    AS
    BEGIN
     SELECT ints, ints*@Int
       FROM @intList;
    END;
    
    GO
    
    DECLARE @t IntList;
    INSERT INTO @t (Ints) VALUES (1),(2),(3);
    
    EXEC dbo.TestProc 2, @t;
    
    GO
    
    DROP PROC dbo.TestProc;
    
    GO
    
    CREATE PROC dbo.TestProc @intList dbo.IntList READONLY, @Int INT
    AS
    BEGIN
     SELECT ints, ints*@Int
       FROM @intList;
    END;
    
    GO
    
    DECLARE @t IntList;
    INSERT INTO @t (Ints) VALUES (1),(2),(3);
    
    EXEC dbo.TestProc @t, 3;
    GO
    
    DROP PROC dbo.TestProc;
    DROP TYPE dbo.IntList;
    
    GO
    

    This creates a UDTT, and then an example sproc which makes uses of it. Then it executes the sproc, returning a data set with the rows of the UDTT multiplied by an integer.

    Then it drops the sproc, and recreates it with the parameters in the other order. Then it executes the sproc, returning a similar data set. Finally it drops the sproc, and the UDTT.