Dynamic query

• htavarez

  SSC Rookie

  Points: 42

  More actions

  February 3, 2003 at 11:02 am

  #80200

  How can I get the results from a dynamic query.

  For example,

  I know of creating a temp table, exec dynamic query, and read result from temp table. Now this solution does not work on a trigger where you cannot create a table.

  Now, the questions still remains.

  Is there anything like?

  @Result = EXEC sp_executesql @query

  FETCH @Result

  Hendrix

• Scorpion_66

  SSCertifiable

  Points: 7891

  More actions

  February 4, 2003 at 8:05 am

  #447977

  Here is an example from one of my procs, where I grab results from a dynamic query:

  set @sql = N'SELECT @val = ImportText FROM ' + @DestTableName2 + ' Where LineNum = ' + Convert(Varchar(50),@CurrRow)

  --Select @sql --debug to check statement

  exec sp_executesql @sql, N'@Val varchar(7500) OUTPUT', @val OUTPUT

  --select @val --Debug for return value

  I have many many other examples if you like, but I bet if you play with this one for just a bit, you will get the idea....

• htavarez

  SSC Rookie

  Points: 42

  More actions

  February 4, 2003 at 8:53 am

  #447987

  Scorpion_66,

  Great idea !!!

  Thank you very much...

  I got it I have done similar stuff before, but never thought of it in this case.

  Thanks again!!!!!!

  Hendrix

  quote:

  ---

  Here is an example from one of my procs, where I grab results from a dynamic query:

  set @sql = N'SELECT @val = ImportText FROM ' + @DestTableName2 + ' Where LineNum = ' + Convert(Varchar(50),@CurrRow)

  --Select @sql --debug to check statement

  exec sp_executesql @sql, N'@Val varchar(7500) OUTPUT', @val OUTPUT

  --select @val --Debug for return value

  I have many many other examples if you like, but I bet if you play with this one for just a bit, you will get the idea....

  ---