Let's say I have an stored procedure defined with a table parameter and table parameter type
CREATE Type dbo.P1 AS TABLE
(
Id Int NOT NULL,
Name nvarchar(50) NULL
)
CREATE PROCEDURE [dbo].[usp_D]
(
@id0 Int,
@P1 dbo.P1 READONLY
)
AS
...
I can call this stored procedure by declaring a table variable
DECLARE @V as dbo.P1
Filling it with data
--tbl_V is just some table with data
INSERT INTO @V (id, name) SELECT id, name FROM tbl_V
and calling the stored procedure
Execute dbo.usp_d
@id0=1, -- some value
@P1=@V
My question: is it possible to pass a query directly to the stored procedure in one step, without separate declaration of the @P1
variable and inserting data into it, something like this:
Execute dbo.usp_d
@id0=1, -- some value
@P1=(SELECT id, name FROM tbl_V)
Table-Valued Parameters aka TVPs are commonly used to pass a table as a parameter into stored procedures or functions. They are helpful in a way, we can use a table as an input to these routines and we can get rid of dealing more complex steps to achieve this process.
First a Table Variable of User Defined Table Type has to be created of the same schema as that of the Table Valued parameter. Then it is passed as Parameter to the Stored Procedure and the Stored Procedure is executed using the EXEC command in SQL Server.
Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.
I believe the simple answer is, you can't.
<Speculative Rant>
This would have been an ideal opportunity for the SqlServer team to have used the Table Valued Constructor syntax, e.g.
Execute dbo.usp_d
@id0=1, -- some value
@P1= VALUES(1, 'Hello');
(Albeit with some typing considerations).
Similarly, it is also not possible to return a Table type from a Table Valued Function, which stymies the opportunity to at least do a Constructor TVF:
Execute dbo.usp_d
@id0=1, -- some value
@P1= (SELECT * FROM dbo.ConstructorFunction(1, 'Hello'));
What would have been nice is Oracle-style nested table constructor syntax, e.g.
Execute dbo.usp_d
@id0=1, -- some value
@P1= TABLE(dbo.P1(1, 'Hello'));
</Speculative Rant>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With