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.
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
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.