August 19, 2014 at 2:22 pm
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
August 19, 2014 at 2:28 pm
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/
August 19, 2014 at 3:16 pm
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
August 20, 2014 at 6:58 am
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 exampleHere 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/
August 20, 2014 at 7:42 am
That is kind of what I thought. I appreciate the help
August 20, 2014 at 7:47 am
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/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply