none
T-SQL, stored procedure using a variable table name

    Question

  • I need to use a variable for a table name, pass it to a stored procedure that extracts data, and then store the result of the sp in another variable.

     

    For example, earlier in the program I construct the table name using this statement:

     

    @tablename = 'OC_TEMPLATE_' + @FORM.  The resulting table name may by something like:  OC_TEMPLATE_101

     

    Now I need to use this table name in a statement like this:

     

    SELECT ITEM FROM @TABLENAME WHERE DEX = @REC

     

    If I use code like this, I can get a result, but I can't store it in a variable:

     

    EXECUTE ('SELECT ITEM FROM  ' + @TABLENAME + ' WHERE DEX = ' + @REC)

     

    Running this give me a value of 309 in the results pane in Management Studio.  309 is the value I want, but I want to now store that in a variable called @ITEM.

     

    I'm trying to run this as a stored procedure where I pass @tablename and @rec into the proc and I want to return @item as an output parameter with the value of 309 (in this case) in @item.  I can't figure out how to configure the proc to give me the output parameter.

     

    Any ideas?

     

    T

    Friday, August 24, 2007 6:23 PM

Answers

  • If you need to capture the output of dynamic SQL check out sp_executesql.

     

     

    WesleyB

    Visit my SQL Server weblog @ http://dis4ea.blogspot.com

    Friday, August 24, 2007 7:38 PM
  • First off, it is not considered a good practice to write code where the tableName is a parameter.  It can be done, but it usually implies that you are storing data in a table name, rather than as a piece of data in a table  In this case, having form as a value in an OC_TEMPLATE table would most likely be the recommendation.  Any kind of variable structures are just messy to work with and often lead to poor performance.  Of course I did say usually, and I stressed it with italics, so as always, your mileage may vary, and you may need to do this.

     

    Adam Machanic has a nice, simple example on this thread:

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=448611&SiteID=1

     

    DECLARE @x int
     
    EXEC sp_executesql
        N'SELECT @x = 1',
        '@x int OUTPUT',
        @x OUTPUT
     
    PRINT @x
     
    You would just need a parm for '@item int' and change your statement to:
     
    'select @item = ...'
    Saturday, August 25, 2007 5:28 AM
    Moderator

All replies

  • If you need to capture the output of dynamic SQL check out sp_executesql.

     

     

    WesleyB

    Visit my SQL Server weblog @ http://dis4ea.blogspot.com

    Friday, August 24, 2007 7:38 PM
  • First off, it is not considered a good practice to write code where the tableName is a parameter.  It can be done, but it usually implies that you are storing data in a table name, rather than as a piece of data in a table  In this case, having form as a value in an OC_TEMPLATE table would most likely be the recommendation.  Any kind of variable structures are just messy to work with and often lead to poor performance.  Of course I did say usually, and I stressed it with italics, so as always, your mileage may vary, and you may need to do this.

     

    Adam Machanic has a nice, simple example on this thread:

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=448611&SiteID=1

     

    DECLARE @x int
     
    EXEC sp_executesql
        N'SELECT @x = 1',
        '@x int OUTPUT',
        @x OUTPUT
     
    PRINT @x
     
    You would just need a parm for '@item int' and change your statement to:
     
    'select @item = ...'
    Saturday, August 25, 2007 5:28 AM
    Moderator
  •  

    As of now, it is not possible to have table value typed parameter in sql server.

    For your requirement, you need not to have the output parameter you can get te result from the output (select statement itself). Use ResultSet or DataSet on your UI.

     

    What is your application platform or langauge?

    Saturday, August 25, 2007 3:11 PM
  • begin tran
    set nocount on
    declare @tablename nvarchar(30) = 'OC_TEMPLATE_101'
    declare @create_table nvarchar(255) = 'create table ' + @tablename + ' (item nvarchar(30), dex nvarchar(30))'
    declare @insert_table nvarchar(255) = 'insert into ' + @tablename + ' values (''test'',''some value'')'
    declare @rec nvarchar(30) = 'some value'
    declare @out nvarchar(30)
    declare @query nvarchar(255) = N'select @out = item from ' + @tablename + ' where dex=@rec'
    exec(@create_table)
    exec(@insert_table)
    exec sp_executesql @query, N'@rec nvarchar (30), @out nvarchar(30) out', @rec = @rec, @out = @out out
    select @out result
    rollback tran

    You'd be better of refactoring and doing something like this:

    set nocount on
    declare @oc_template table (id int not null primary key)
    declare @oc_template_data table (id int not null identity (1,1) primary key, template_id int, item nvarchar(30), dex nvarchar(30))
    declare @form int = 101
    declare @rec nvarchar(30) = 'some value'
    declare @out nvarchar(30)
    insert into @oc_template values (@form)
    insert into @oc_template_data values (@form, 'test', 'some value')
    select @out = item from @oc_template_data where template_id = @form and dex=@rec
    select @out
    Thursday, August 16, 2012 5:24 PM