Is it possible to call a T-SQL (SQL Server 2012) stored procedure with more than one table-valued parameter?
I.e.
CREATE Type dbo.P1 AS TABLE
(
Id Int NOT NULL,
Name nvarchar(50) NULL
)
CREATE Type dbo.P2 AS TABLE
(
Id Int NOT NULL,
Name nvarchar(50) NULL
)
CREATE PROCEDURE [dbo].[D]
(
@id0 Int,
@P1 dbo.P1 READONLY,
@P2 dbo.P2 READONLY
)
AS
...
I am getting an error
Must declare the scalar variable "@P2"
Stored procedures can accept multiple parameters and user-defined type parameters are not any different from a SQL Server system type parameters in this case.
If you only execute the following code it will allow you to create a procedure without any errors, which explains that SQL Server does allow us to create procedures accepting multiple user defined type parameters.
--------------------Test ---------------------------------
CREATE Type dbo.P1 AS TABLE
(
Id Int NOT NULL,
Name nvarchar(50) NULL
)
GO
CREATE Type dbo.P2 AS TABLE
(
Id Int NOT NULL,
Name nvarchar(50) NULL
)
GO
CREATE PROCEDURE [dbo].[D]
(
@id0 Int,
@P1 dbo.P1 READONLY,
@P2 dbo.P2 READONLY
)
AS
BEGIN
SET NOCOUNT ON;
SELECT 'Debugging';
END
GO
The error exists somewhere else in your code where you are trying to use a variable which you havent declared. and since you already have a Variable called @P2 once you have found that variable use a different name for that variable.
