table variables and dinamic sql question

• Tudor Gabriel

  Mr or Mrs. 500

  Points: 533

  More actions

  July 7, 2006 at 12:06 am

  #168949

  hi, i need to know if there is a way to use a table variable in a dinamic sql.

  something like below:

  declare @t table(id uniqueidentifier)

  EXEC('select * from @t')

  i know that about the dinamic sql scope. but maybe there is a workaround ...

  it is posible to use temp tables, but they cause a recompile and lots of cache miss in my proc, while table variables are ok.

  i tryed to declare the table inside then use it outside the query, ofcourse it doesnt work, i even tryied to use sp_executeSQL and sending the table as a param, this doesnt work either.

  so is there a way to use a table variable inside a procedure and inside dinamic sql?

• mark lake

  Say Hey Kid

  Points: 702

  More actions

  July 7, 2006 at 5:40 am

  #647933

  create procedure select_table

  @table varchar(100)

  as

  declare @sql varchar(1000)

  select @sql='select * from'+@table

  exec(@Sql)

• Tudor Gabriel

  Mr or Mrs. 500

  Points: 533

  More actions

  July 10, 2006 at 2:42 am

  #648174

  i think there is something wrong in there .... but probably i wasn't clear enough

  i don't need to use a dinamically defined table (which phisicaly exists inside the database) in the dinamic sql, but a Table Variable. From your post i see that you are sending the table name as the parameter.