CTE populating TVP parameter

Question

CTE populating TVP parameter

JKSQL

SSCrazy Eights

Points: 8481
More actions
August 19, 2014 at 2:22 pm

#294842

The stored procedure accepts a TVP table as a parameter. Will something like this work?
BEGIN
;with tree(Id) as
(
SELECT 1
UNION ALL
SELECT 2
)
EXECUTE [dbo].[Get_FooByIds] @tvpId = tree
END

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

JKSQL (8/19/2014)
The stored procedure accepts a TVP table as a parameter. Will something like this work?
BEGIN
;with tree(Id) as
(
SELECT 1
UNION ALL
SELECT 2
)
EXECUTE [dbo].[Get_FooByIds] @tvpId = tree
END

Have you tried it?

hint...no it won't work as you have it coded.

You have to insert into your user defined table type. Something like this.

declare @MyTree MyTVP

;with tree(Id) as

(

SELECT 1

UNION ALL

SELECT 2

)

insert @MyTree

select Id from tree

EXECUTE [dbo].[Get_FooByIds] @tvpId = @MyTree

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 2

Yes, you have shown me my question was not clear. The question I have is a CTE would be lighter than a TVP Insert/Delete correct? If so can I make a CTE work with a TVP parameter. Hopefully this is a better example

Here is the TVP example

declare @MyTree MyTVP

If @SomeIdentifier = 1

BEGIN

insert @MyTree

SELECT Id from Table Where Id = 1

EXECUTE [dbo].[Get_FooByIds] @tvpId = @MyTree

DELETE @MyTree

END

If @SomeIdentifier = 2

BEGIN

insert @MyTree

SELECT Id from Table Where Id = 2

EXECUTE [dbo].[Get_FooByIds] @tvpId = @MyTree

DELETE @MyTree

END

Vs here is a CTE which is truly a temporary object

If @SomeIdentifier = 1

BEGIN

;with tree(Id) as

(

SELECT Id from Table Where Id = 1

)

EXECUTE [dbo].[Get_FooByIds] @tvpId = tree

END

If @SomeIdentifier = 2

BEGIN

;with tree(Id) as

(

SELECT Id from Table Where Id = 2

)

EXECUTE [dbo].[Get_FooByIds] @tvpId = tree

END

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

JKSQL (8/19/2014)
Yes, you have shown me my question was not clear. The question I have is a CTE would be lighter than a TVP Insert/Delete correct? If so can I make a CTE work with a TVP parameter. Hopefully this is a better example
Here is the TVP example
declare @MyTree MyTVP
If @SomeIdentifier = 1
BEGIN
insert @MyTree
SELECT Id from Table Where Id = 1
EXECUTE [dbo].[Get_FooByIds] @tvpId = @MyTree
DELETE @MyTree
END
If @SomeIdentifier = 2
BEGIN
insert @MyTree
SELECT Id from Table Where Id = 2
EXECUTE [dbo].[Get_FooByIds] @tvpId = @MyTree
DELETE @MyTree
END
Vs here is a CTE which is truly a temporary object
If @SomeIdentifier = 1
BEGIN
;with tree(Id) as
(
SELECT Id from Table Where Id = 1
)
EXECUTE [dbo].[Get_FooByIds] @tvpId = tree
END
If @SomeIdentifier = 2
BEGIN
;with tree(Id) as
(
SELECT Id from Table Where Id = 2
)
EXECUTE [dbo].[Get_FooByIds] @tvpId = tree
END

Again did you try it? It still won't work. A cte is just a temporary view. You can't just assign it to a datatype. That is not how this works. The cte code you are trying is the same thing as this.

create view tree

(

select Id from Table

)

go

EXECUTE [dbo].[Get_FooByIds] @tvpId = tree

Obviously that isn't going to work. When you have a table datatype is it empty until you insert rows into it. You can't just implicitly cast some resultset (in this case your cte) to a table.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 4

That is kind of what I thought. I appreciate the help

Sean Lange SSC Guru Points: 286609 More actions · Answer 5

JKSQL (8/20/2014)
That is kind of what I thought. I appreciate the help

You're welcome. Hope it makes sense.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

CTE populating TVP parameter

Cookies on SQLServerCentral