Stored Procedure: turn Table name into Table Variable

Question

There are quite a few similar Questions, however i didn't quite find what i was looking for. Since using dynamic SQL in a stored procedure can quickly get cumbersome, I want to pass a table name (Varchar) to a stored procedure, turn that Tablename into a Tablevariable and afterwards work with this Tablevariable for the rest of the procedure. I can't figure out the code for this.

I'm working in SSMS on a SQL Server 2008R2. Currently my code looks similar to this. I lag the middle part to create the @Table Table Variable from the @TableName Varchar Variable

CREATE Procedure [dbo].StoredProc(@Tablename Varchar)
AS
Begin
Declare @Table Table (ColA Varchar, ColB Float)
Declare @result float

-- Something like Insert @Table Select * From @Tablename using Dynamic sql or sth. similar

Select @result = Select sum(ColB) From @Table
End

Check this post http://stackoverflow.com/questions/5877207/stored-procedure-with-table-name-as-parameter — kelsier, Jun 04 '14 at 11:27
I've seen this, but i don't understand, where the result of this executed select query is stored and how i can access this resultset at a later point in the procedure. — Hellvetia, Jun 04 '14 at 11:36
Look at this http://stackoverflow.com/questions/803211/how-to-get-sp-executesql-result-into-a-variable — kelsier, Jun 04 '14 at 11:41

score 0 · Answer 1 · edited Jun 04 '14 at 12:13

0

you should set the statement yo need in a variable:

 SET @sqlString='INSERT ' + @Table + ' SELECT * FROM ' + @Tablename

using Dynamic sql or sth. similar"

and then execute it:

 EXEC sp_executesql @sqlString

edited Jun 04 '14 at 12:13

YvesR

5,922
6
43
70

answered Jun 04 '14 at 11:39

ElenA

78
6

I doubt it would work. dynamic SQL statement donno's about local @Table variable – xacinay Jun 04 '14 at 11:48

xacinay · Accepted Answer · 2014-06-04T11:59:17.510

0

You can combine dynamic SQL and Temporary table storage the following way:

CREATE Procedure [dbo].StoredProc(@Tablename Varchar(100))
    AS
    Begin
    create table #TempTbl (ColA Varchar(100), ColB Float);
    Declare @result float

    declare @dynSQL varchar(max);
    select @dynSQL = 'insert into #TempTbl select 
      cast(val1 as varchar(100)) as ColA, 
      cast(val2 as float) as ColB from ' + COALESCE( @Tablename, 'NULL');
    -- Tablename should contain schema name, 'dbo.' for example
    exec( @dynSQL );
    Select @result = sum(ColB) From #TempTbl
    drop table #TempTbl;
    return @Result;
    End

edited Jun 04 '14 at 11:59

answered Jun 04 '14 at 11:52

xacinay

881
1
11
29

Just build your code into my procedure and this works great. Thank you very much. Not quite sure what the added value of the COALESCE is though. – Hellvetia Jun 04 '14 at 12:18
`COALESCE` part added to insure you from NULL @dynSQL value for NULL-valued @tablename. Should be always careful forming dynamic SQL by concatenating variables, that can be NULL. Remember that it wll always lead you to NULL sql string executed. – xacinay Jun 04 '14 at 12:23

score 0 · Answer 3 · answered Jun 06 '14 at 02:27

0

USE AdventureWorks2012;

DECLARE @TableName NVARCHAR(MAX);

DECLARE @Copy TABLE
(
    [ShiftID] [tinyint] NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [StartTime] [time](7) NOT NULL,
    [EndTime] [time](7) NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
);

SET @TableName = N'[HumanResources].[Shift]';

INSERT INTO @Copy
EXEC ('SELECT * FROM ' + @TableName);

SELECT * FROM @Copy;

answered Jun 06 '14 at 02:27

Bruce Dunwiddie

2,888
1
15
20

This approach works aswell and is actually closer to my question since i wanted a Table Variable and not a Temp Table. I tested my Procedure with Table Variables aswell and the runtime (in my case) turned out to be exactly the same with Table Variable as it is with Temporary Table. I personally kept the implementation with Temporary Tables. – Hellvetia Jun 11 '14 at 15:29
Ya, temp tables and table variables ultimately are the same. I just rarely use temp tables anymore because of the lack of meta data returned to callers like SSIS, SSRS, .Net, etc, and how they can get in the way of plan caching. The only real use of a temp table I see anymore is if you need any more complex indexing than a primary key because that's your only option with table variables. – Bruce Dunwiddie Jun 13 '14 at 04:05

Stored Procedure: turn Table name into Table Variable

3 Answers3

Linked