Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Passing a subquery directly to a table type parameter in USP in SQL Server

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)
like image 666
Alex Lizz Avatar asked Mar 18 '14 17:03

Alex Lizz


People also ask

How do you pass a table as a parameter in SQL?

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.

How do you execute a stored procedure with user-defined table type in SQL?

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.

How do you use table valued parameters?

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.


1 Answers

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>

like image 96
StuartLC Avatar answered Oct 22 '22 22:10

StuartLC