none
Obtain table output table variable in a dynamic tsql RRS feed

  • Question

  • Hy,

    if is possible i need to use sp_executesql with my "self costructed t-sql statement'" and retur the result of "inside select " througth table variable .

    For esample:

    declare @table table (uno int , due int);
    DECLARE @ParmDefinition nvarchar(500);
    set @ParmDefinition = N'@table (uno int , due int) table'
    DECLARE @TSQL nvarchar(4000)
    
    set @TSQL = 
    N'
    insert into @table (uno,due)
    select 1,2
    select * from @table'
    
    EXEC sp_executesql 
           @sel_query = @TSQL, 
           @params = @ParmDefinition,
           @table = @table OUTPUT 

     

    From here I would liketo use the variable 

    @table

    Marco Bosco

    Tuesday, March 20, 2012 8:36 AM

Answers

  • While table valued parameters to SPs are readonly, table valued functions can return table-typed variables just fine.  (See this entry in the library: http://msdn.microsoft.com/en-us/library/ms186755.aspx).

    Tuesday, March 20, 2012 2:15 PM
  • You should pass the temp table as parameter. Just define it before you call
    the batch.

    CREATE TABLE #temp (...)

    set @TSQL = N'
    insert into #table (uno,due)
    select 1,2
    select * from my_table'

    EXEC sp_executesql
           @sel_query = @TSQL

    select * from #table


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi NModerator Tuesday, March 20, 2012 11:12 PM
    • Marked as answer by marcobosco Thursday, March 22, 2012 7:48 AM
    Tuesday, March 20, 2012 2:14 PM

All replies

  • DECLARE @TSQL nvarchar(4000)


    set @TSQL = 
    N' DECLARE @table table (uno int , due int) 
    insert into @table (uno,due)
    select 1,2
    select * from @table'






    EXEC sp_executesql 
           @sel_query = @TSQL

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Proposed as answer by rubixcuban Tuesday, January 10, 2017 9:18 PM
    Tuesday, March 20, 2012 8:53 AM
    Answerer
  • Thanks Uri,

    but my need is use the @table after the call to sp_executesql 

    Like this:

    --....here the solution of the problem...

    EXEC sp_executesql 
           @sel_query = @TSQL

    ---From here i need to use the table parameter...i need to recover the table value like a output parameter....

    Select * from @TSQL


    Marco Bosco

    Tuesday, March 20, 2012 9:04 AM
  • You can pass a table variable as a parameter to dynamic SQL if you define a table type. However, it is not going to help you in this case, because table-valued parameters are read-only.

    Your best is probably to use INSERT-EXEC. If your dynamic SQL is complex and you fill in the table in several places, use a temp table instead.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, March 20, 2012 9:31 AM
  • Perhaps if you explain the logic we would provide solution without using dynamic sql at all

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, March 20, 2012 9:35 AM
    Answerer
  • I explain you maybe is simple.

    I need a dynamic tsql because the table name of select query inside of dyn-query is a variable thet i pass.

    All is incapsulated in a SP.

    I need to call this sp from a .Net application and i need (entity framework) to obtain the metadato for the result set because creation of complex type in entity framework.

    But because the sq inside have a dyn-tsql can't retrive the metadata....so i think....

    I pass a table variable in a dyn-string that the dyn-execution populate and so i retrive the table parameter like OUTPUT parameter

    After in my SP simply call a select on the table variable that could provide the full metadata need ...

    thanks,

    Marco


    Marco Bosco

    Tuesday, March 20, 2012 11:16 AM
  • Perhaps it is enough.
    But I could not get the variable of type table, as the output variable....

    thanks,

    Marco Bosco


    Marco Bosco

    Tuesday, March 20, 2012 11:17 AM
  • But I could not get the variable of type table, as the output variable....

    As I said, table-valued parameters are readonly. Very, very unfortunate, but that's the way it is.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, March 20, 2012 11:20 AM
  • Excuse Erland, but for me is not so very simple understand...mybe also for the english.... :( ... however...based on my explanation...can you help me?

    (however based on your explaination ..."table variable parameter are only readonly" ....i think.... i define and populate the the table parameter inside the dyn statement and so a retrive the table param already populate....is not possible?

    thank,

    Marco 


    Marco Bosco

    Tuesday, March 20, 2012 11:52 AM
  • You can pass a table-valued parameter to sp_executesql, but only for input values. You must say:

    EXEC sp_executesql @sql, N'@t mytabletype READONLY', @t

    È possible di passare un parameter di tavola a sp_executesql, ma solamente per leggere, non per scrivere. Bisogna usare INSERT-EXEC od un tavola temporare (#temp) invece di un variable.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, March 20, 2012 1:14 PM
  • Thanks,

    i try my i don't understand...can you help?

    declare #table table (uno int , due int);
    DECLARE @ParmDefinition nvarchar(500);
    
    DECLARE @TSQL nvarchar(4000)
    declare @tablename nvarchar(128)
    set @tablename = 'my_tablename'
    set @ParmDefinition = N'#table (uno int , due int) table;@tablename nvarchar(128)'
    
    set @TSQL = N'
    insert into #table (uno,due)
    select 1,2
    select * from my_table'
    
    EXEC sp_executesql 
           @sel_query = @TSQL, 
           @params = @ParmDefinition,
           #table = #table OUTPUT 
    
    select * from #table


    Marco Bosco

    Tuesday, March 20, 2012 2:08 PM
  • You should pass the temp table as parameter. Just define it before you call
    the batch.

    CREATE TABLE #temp (...)

    set @TSQL = N'
    insert into #table (uno,due)
    select 1,2
    select * from my_table'

    EXEC sp_executesql
           @sel_query = @TSQL

    select * from #table


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi NModerator Tuesday, March 20, 2012 11:12 PM
    • Marked as answer by marcobosco Thursday, March 22, 2012 7:48 AM
    Tuesday, March 20, 2012 2:14 PM
  • While table valued parameters to SPs are readonly, table valued functions can return table-typed variables just fine.  (See this entry in the library: http://msdn.microsoft.com/en-us/library/ms186755.aspx).

    Tuesday, March 20, 2012 2:15 PM
  • EXEC sp_executesql @sel_query = @TSQL, @params = @ParmDefinition, #table = #table OUTPUT

    That is invalid syntax.

    You can do INSERT EXEC to get the result set of a dynamic SQL query:

    CREATE TABLE #Prod (ID int, Pname varchar(64))
    GO
    INSERT #Prod
    EXEC sp_executeSQL N'SELECT ProductID, Name 
                        FROM AdventureWorks2008.Production.Product'
    GO
    -- (504 row(s) affected)
    SELECT * FROM #Prod
    GO
    DROP TABLE #Prod    

    Dynamic SQL article:
    http://www.sqlusa.com/bestpractices/dynamicsql/

    INSERT EXEC example:

    http://www.sqlusa.com/bestpractices2008/list-files-in-directory/


    Kalman Toth
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

    Tuesday, March 20, 2012 2:22 PM
    Moderator
  • you can use global temp table



    exec ('select * into ##t from sys.tables')
    select * from ##t

    Tuesday, March 20, 2012 2:56 PM