SET TABLE VARIABLE FROM FUNCTION CALL

Question

Post reply

SET TABLE VARIABLE FROM FUNCTION CALL

mar.ko

Default port

Points: 1451
More actions
August 18, 2015 at 2:28 pm

#398919

Guys - is there a better way to do this ?
INSERT INTO @TESTTAB SELECT * FROM dbo.UTIL_TABLE_FROM_STRING(@szDelimit)
Note....The stored function returns a table.
Why doesn't this work ?:
SET @TESTTAB = (SELECT * FROM dbo.UTIL_TABLE_FROM_STRING(@szDelimit))
I wonder if I need to establish a user-defined tabletype ? Will that do it ?
I really just want a pointer to the table, and not to have to create a new copy.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

mar.ko (8/18/2015)
INSERT INTO @TESTTAB SELECT * FROM dbo.UTIL_TABLE_FROM_STRING(@szDelimit)

No, not without changing the code around it (which may be something worth considering)

Why doesn't this work ?:
SET @TESTTAB = (SELECT * FROM dbo.UTIL_TABLE_FROM_STRING(@szDelimit))

Table variables aren't exactly variables, they can't be passed around or assigned to each other. They're temp tables with different scope rules.

I wonder if I need to establish a user-defined tabletype ? Will that do it ?

No.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

If you need to reference that table in multiple places, just load it into a temp table, global temp table or even a regular table in the tempdb db. For example:

IF OBJECT_ID('tempdb.dbo.#test_table') IS NOT NULL

DROP TABLE #test_table

SELECT * INTO #test_table FROM dbo.UTIL_TABLE_FROM_STRING(@szDelimit)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

mar.ko Default port Points: 1451 More actions · Answer 3

Thanks Gila and Scott.....good stuff.

Interestingly - by using a temp table instead of a declared table variable, the @@FETCH_STATUS initializes to -1 versus zero.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

mar.ko (8/19/2015)
Thanks Gila and Scott.....good stuff.
Interestingly - by using a temp table instead of a declared table variable, the @@FETCH_STATUS initializes to -1 versus zero.

@@FETCH_STATUS??? Correct me if I'm wrong but it sounds like you're using a cursor. If so, why do you think you need a cursor?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

James_R_Alves SSC Veteran Points: 259 More actions · Answer 5

As to your question...

Looks like table types will work with table variables to get to what you're after:

CREATE TYPE ttypeTestTab AS TABLE (

TABLE_CATALOG NVARCHAR(128) NULL

, TABLE_SCHEMA NVARCHAR(128) NULL

, TABLE_NAME sysname NOT NULL

, COLUMN_NAME sysname NULL

, ORDINAL_POSITION INT NULL

);

GO

DECLARE @TestTab AS [dbo].[ttypeTestTab]

INSERT INTO @TestTab

(

TABLE_CATALOG

, TABLE_SCHEMA

, TABLE_NAME

, COLUMN_NAME

, ORDINAL_POSITION

)

SELECT TOP 10

TABLE_CATALOG

, TABLE_SCHEMA

, TABLE_NAME

, COLUMN_NAME

, ORDINAL_POSITION

FROM

[INFORMATION_SCHEMA].[COLUMNS]

SELECT * FROM @TestTab

There may be a performance benefit to using table vars over # temp tables as well.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

James_R_Alves (8/24/2015)
There may be a performance benefit to using table vars over # temp tables as well.

A negative one usually, due to the lack of statistics.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

GilaMonster (8/24/2015)
James_R_Alves (8/24/2015)
There may be a performance benefit to using table vars over # temp tables as well.
A negative one usually, due to the lack of statistics.

...and execution plan estimates of 1 row.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bill Talada SSChampion Points: 11954 More actions · Answer 8

mar.ko (8/18/2015)
Guys - is there a better way to do this ?
INSERT INTO @TESTTAB SELECT * FROM dbo.UTIL_TABLE_FROM_STRING(@szDelimit)
Note....The stored function returns a table.
Why doesn't this work ?:
SET @TESTTAB = (SELECT * FROM dbo.UTIL_TABLE_FROM_STRING(@szDelimit))
I wonder if I need to establish a user-defined tabletype ? Will that do it ?
I really just want a pointer to the table, and not to have to create a new copy.

Better way? Depends on what you mean by better. UDTTs are stored in tempdb and can have primary key and unique constraints and thus are indexed.

Why doesn't the SET statement work? Because the "pointer assignment" you're trying to do only works when passing a UDTT as a parameter. And even then it comes in as a readonly table (readonly pointer).

Personally I love UDTTs for the elegant object-like code they enable one to write.