Assign Function Results to a Table Variable? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Assign Function Results to a Table Variable?

Author

Topic

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-11-12 : 13:36:40

Is there any way to assign the results of a table valued function to a table variable other than to do an INSERT INTO @tmp SELECT...? I tried doing a regular assignment, but it failed.

I want to put the results of the function in a table variable, because the same results are used in multiple places in the same SP. So rather than run the query again each time, I just want to run it once, and use it from there. Is what I'm looking to do possible any other way?

Thanks,
Steve

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-12 : 13:41:26

Is the function returing one column? If so, why not have the function return a scalar value instead?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-12 : 13:57:29

Steve,

why not post the code...

Something like

USE Northwind
GO

SELECT @x = DATEPART(dw,GetDate())

If it's more like

SELECT @x = DATEPART(dw,ShippedDate) FROM Orders

Then you'll have a problem...

Brett

8-)

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-11-12 : 14:43:36

No, it's not scalar. It's all for a multi-delimited parsing function that returns a table. The procedure goes something like this...


begin tran
--Stash the Subscriptions in a temp var so that we only have to parse once
Declare @ParsedSubscriptions table (
	SubscriptionDatabaseID int,
	SubscriberID int
)
INSERT INTO @ParsedSubscriptions
SELECT Value FROM ::fn_multidelimitedtotable(@Subscriptions, ',', '=')
--Remove any Subscriptions that aren't in the list
DELETE FROM dbo.trn_tblDatabaseSubscriptions 
WHERE SubscriberDatabaseID = @DatabaseID 
	And DatabaseID Not In (SELECT S.SubscriptionDatabaseID FROM @ParsedSubscriptions S)
--Check for errors
if (@@ERROR = 0) begin
	--Insert the new subscriptions
	INSERT INTO dbo.trn_tblDatabaseSubscriptions (
		DatabaseID,
		SubscriberDatabaseID
	) SELECT
		S.SubscriptionDatabaseID,
		@DatabaseID
	FROM @ParsedSubscriptions S

... snipped the rest

From here the query goes on to do quite a few more operations, about 4 of which require the parsed list values. So rather than call the parsing function for each time I need to list values, I'm just trying to store it to save additional processing time.

Steve

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-12 : 14:50:26

You must say "INSERT INTO". You can't say "SET @T = (Select ....)" if @T is a table variable.

surely that shouldn't be too big of a problem. if it helps to understand, think of a table variable as being more in the "temp table" family rather than in the "variable" family.

- Jeff

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-11-12 : 16:28:18

Yeah, the INSERT INTO was the only way I found that it could be done. I was hoping that there was some highly optimized method for doing it in SQL Server to just do it as an assignment.

Thanks guys

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-12 : 16:30:18

Balstrix, why wouldn't INSERT INTO be optimized?

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-12 : 16:44:21

I see what he's saying ... is there a way to do a "memory copy" of the contents of a table variable to another table variable, as opposed to using SQL statements. SQL statements would require all data to be processed by the usual SQL Server engine and libraries and all that, whereas a straight memory copy would be much quicker since it would be basically be limited only by the speed of the memory.

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-12 : 16:45:34

Ah, I see now.

Tara

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-11-12 : 20:45:55

Since a table variable is (almost always) contained entirely in RAM, you're doing a memory copy anyway. Granted more cycles are being churned to do it, but it's still a lot faster than going to and from disk. Even then, most compilers can't copy an array without enumerating through it anwyay, so it's probably not that much more efficient than how SQL Server handles it.

I know I mentioned Ken Henderson's new book a lot already:

http://weblogs.sqlteam.com/robv/posts/448.aspx

But you should check it out, you'd be surprised at how they've optimized many operations to be as efficient as possible.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources