1

I Have a UDF that returns a TABLE.

In my SP i define a temp table variable:

declare @t table ([ID] uniqueidentifier)

How do I set @t = MyDF?

Only method that works is:

INSERT INTO @t SELECT * FROM dbo.MyUDF(@List)

This does not work:

SELECT * INTO @t FROM dbo.MyUDF(@List) -- <- This is just a note, and NOT the question

My Question: Can't I just somehow assign select @t = dbo.MyUDF?
Is there any other way to directlly assign the UDF result TABLE to the temp variable other than actually "INSERT INTO"/"SELECT INTO"

EDIT: I know I can reference dbo.MyUDF() in my query directly without using a temp table variable. but since I need to reference this a few times in my SP, I prefer to work with a local variable which will be assigned only once, and not process dbo.MyUDF() each time I reference it.

My UDF is very much the same as in this answer: https://stackoverflow.com/a/11105413/3933402 (SplitInts - only mine returns uniqueidentifier)

Community
  • 1
  • 1
user3933402
  • 93
  • 6
  • No, to get data into your table variable, you're going to have to insert it. Why is this a problem? – Aaron Bertrand Aug 19 '14 at 16:29
  • 2
    The other option, of course, is to not use a table variable in the first place, and just reference dbo.MyUDF() in your query directly. – Aaron Bertrand Aug 19 '14 at 16:30
  • @AaronBertrand, b/c the additional INSERT costs performance. I thought since the UDF already returns a TABLE object I could reference it. I need to reference `dbo.MyUDF()` (@t) a few times in that SP. so the whole process in that UDF will run again and again when I reference it (? - not sure) – user3933402 Aug 19 '14 at 16:38
  • 2
    Why do you think SELECT INTO or SELECT @t = wouldn't incur the same performance "problem"? Have you validated that any of this is actually a problem, or are you prematurely optimizing? Instead of worrying about the cost of inserting into a table variable or how you're going to reference the UDF in your query, maybe you should focus on optimizing the UDF itself - e.g. make sure it is schemabinding, is an inline table-valued UDF and not a multi-statement one, and that the work really needs to be done by a UDF in the first place... – Aaron Bertrand Aug 19 '14 at 16:53
  • @AaronBertrand, I actually need to impliment this: http://stackoverflow.com/a/11105413/3933402 (SplitInts). It's your answer ("focus on optimizing the UDF itself") :) And my question was rather simple: is there any other way to assign the result TABLE to the temp variable other than actually "INSERT INTO" – user3933402 Aug 20 '14 at 08:14

1 Answers1

-1

could you try SELECT * INTO #t FROM dbo.MyUDF(@List)

Hlin
  • 134
  • 4