Table Variables
-
Anyone know how to dynamically create a table variable that can be used in the scope of a stored procedure? I can create the table variable in a dymanically created EXEC(@SQL) command but will not have access to it in the rest of the stored procedure. Building the entire commad into a @Sql String to be executed would be interesting but painful.
My problem could also be sloved by being able to add columns to a table variable. Everything I am finding says it's not possible.
I know I can code my store procedure using temp tables but I am avoiding this due to speed issues.
Thanks in advance,
Dallen
-
I had a great deal of difficulty with this; I was trying to populate variables in which the name would change dynamic (via a loop) and hence the value would need to be different.
I ended up throwing the incoming variable into a #temp table and used code similar to the following to get my values where I could. See if this helps.
DECLARE @ConstraintSQL nvarchar(2000),
@Variable varchar(200)
SELECT @Counter = 2
SELECT @SQL = ''
WHILE @Counter <= 6
BEGIN
SET @ConstraintSQL = N'SET @Variable = ( SELECT code' + CONVERT( varchar(1), @Counter) + ' FROM #TempTable) '
EXEC sp_executesql @ConstraintSQL, N'@Variable varchar(200) OUTPUT', @Variable OUTPUT
SET @Variable = @Variable -- this was simply to insure the correct value was being captured
END
I wasn't born stupid - I had to study.
-
If you create a table variable in a varchar and then EXEC it, you won't be able to address the table variable back in the calling stored proc, since by the time control has returned to your proc the variable will be out of scope. Can you create a base table with generic columns for what you need? If it's something that runs every hour or minute, it might be better to trade performance for strong data typing.
There is no "i" in team, but idiot has two. -
What about a udf which returns a table?
-
Can you tell us specifically what you're trying to acheive...
The way I read this, if you can do it using temporary tables then you should be able to do the exact same thing with table variables so I can't see the difficulty.
I guess I've missed the point of your problem...
Sam
-
Hi,
I have the same problem as Sam, if you can do it with temp tables - you should be able to do it with table variables.
There are plenty of existing scripts and discussions on the pro's and cons of temp tables vs table variables, which should go some way to helping you make the right decision on the way forward. e.g.
http://www.sqlservercentral.com/scripts/contributions/736.asp
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=65&messageid=119210
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=15107
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=65&messageid=17656
Just do a search on Table Variables on the home page.
Have fun
Steve
We need men who can dream of things that never were.
-
I am creating a stored procedure that takes in a list of product IDs and returnes a table with products as the rows and product attributes as the columns. I need this table to be dynamically created with the attribute names as the column names. Products may or may not have all of the possiable attributes so the number of columns change by list of products.
I tried to create the table variable first and add the new columns while looping thought the attributes but table variable do not support alter methods.
What I want to do now is loop through all the attributes and create the table variable dynamically. I have done this with a EXEC(@Sql) command but do not know how to get the created table variable out of the exec scope.
Hope this is enough information.
Thanks
-
I have similar trouble using a TABLE data_type. The TABLE datatype has local scope and can't be passed as a parameter.
I wanted to return a list of table names in a TABLE data_type where the database name was a parameter. The argument of a stored procedure or user-define fn can't be of the TABLE data_type. Since the TABLE data_type has scope, it can't be declared and then used in a sp_executsql. I had to create a temporary table and then use EXECUTE to populate it. I could then load a TABLE data_type variable with the results from the temporary table. Vary round about way to get what I want.
Am I missing something? Is there a better method? Can the use of the temporary table be avoided?
SET NOCOUNT ON
DECLARE @dbname sysname
DECLARE @query varchar(4000)
DECLARE @Tables TABLE
(
[name] sysname
)
SET @dbname = 'pubs'
IF ISNULL(@dbname, '') = '' SELECT @dbname = db_name()
-- Load a temp table with the list of table names
CREATE TABLE #tables
(
[name] sysname
)
SET @query = 'use ' + @dbname + '; '
+ 'insert into #tables '
+ 'select so.[name] '
+ 'from sysobjects so, '
+ 'sysindexes si, '
+ 'sysusers su '
+ 'where '
+ 'so.[id]=si.[id] and '
+ 'so.[uid]=su.[uid] and '
+ 'so.type=''U'' and '
+ 'si.indid < 2 and '
+ 'si.[rows] > 0 and '
+ 'su.uid = 1 -- where owner is ''dbo'' '
EXECUTE(@query) -- #tables will be in scope, @tables is not
-- Put the data into the TABLE data_type variable
INSERT INTO @Tables
SELECT * FROM #tables order by [name]
DROP TABLE #tables
SELECT * FROM @Tables -- verify data present
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
-
Dallen,
I see what you mean now.
Sorry but the answer is no, you can't pass a table variable (or any other variable for that matter) outside of the batch or stored proc that it's declared in. So as far as I can see, unless you can think of a different strategy (and from experience I'd say that there normally is at least one other way of doing anything) the only options you have is a temporary or a "real" table.
Temporary tables aren't all bad though. There's been lots of discussions about the relative merits of temp v variable tables and they both have advantages and disadvantages.
Sorry this isn't the answer you were hoping for.
Sam
-
Yep,
If sp_executesql would accept the table variable as a parameter, you would be out the tunnel.
Unfortunately it only accepts parameters that are Unicode or can be explicitly converted to ntext. @Table variables can't..........
Bit of a pain but - looks like you will have to find an alternative Dallen
Have fun
Steve
We need men who can dream of things that never were.
-
What about using a global temporary table ...
-
Good grief don't even think about using global temp tables.... the person who designed that bit of functionality had horns growing out of his head and a forked tail....!
Errrr..... whew, sorry for that rant, back to normal now!
Sam
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply