saving results of exec(@sql) to a variable

• Edward-445599

  Hall of Fame

  Points: 3147

  More actions

  May 8, 2009 at 6:32 am

  #221018

  Hi,

  How you do the following? what would be the correct syntax for the last line will not work

  declare @DateTime datetime

  declare @tablename varchar(100)

  declare @sql varchar(8000)

  set @tablename = 'perf270409'

  set @sql = 'select min(starttime) from ' + @tablename

  set @DateTime =exec(@sql) --HOW CAN I DO THIS?

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  May 8, 2009 at 7:07 am

  #991039

  Something like this should work:

  declare @DateTime datetime

  declare @tablename varchar(100)

  declare @sql varchar(8000)

  declare @temp table(starttime datetime)

  set @tablename = 'perf270409'

  set @sql = 'select min(starttime) from ' + @tablename

  insert @temp(starttime)

  exec(@sql)

  select @DateTime = starttime from @temp

  select @DateTime

  --Ramesh
  

  ---

• Edward-445599

  Hall of Fame

  Points: 3147

  More actions

  May 8, 2009 at 7:46 am

  #991077

  I get the following error

  EXECUTE cannot be used as a source when inserting into a table variable.

• Edward-445599

  Hall of Fame

  Points: 3147

  More actions

  May 8, 2009 at 7:50 am

  #991084

  hi Its okay, i have used a temp table instead. Thanks for the help you got me going on the right track.

  declare @DateTime datetime

  declare @tablename varchar(100)

  declare @sql varchar(8000)

  create table #temp (starttime datetime)

  set @tablename = 'perf270409'

  set @sql = 'select min(starttime) from ' + @tablename

  insert #temp(starttime)

  exec(@sql)

  select @DateTime = starttime from #temp

  select @DateTime

• Madhivanan-208264

  SSCertifiable

  Points: 7516

  More actions

  May 8, 2009 at 7:52 am

  #991090

  Also read about sp_executesql in SQL Server help file

  ---

  Madhivanan

  Failing to plan is Planning to fail

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  May 8, 2009 at 8:15 am

  #991118

  It can also be done using sp_executesql system procedure

  declare @DateTime DATETIME

  declare @tablename varchar(100)

  declare @sql nvarchar(4000)

  create table #temp (starttime datetime)

  -- Using sp_executesql

  set @sql = 'select @DateTime = min(starttime) from ' + @tablename

  execute sp_executesql @sql, N'@DateTime datetime output', @DateTime = @DateTime output

  select @DateTime

  --Ramesh
  

  ---

• Madhivanan-208264

  SSCertifiable

  Points: 7516

  More actions

  May 8, 2009 at 8:23 am

  #991123

  Ramesh (5/8/2009)

  ---

  It can also be done using sp_executesql system procedure

  declare @DateTime DATETIME

  declare @tablename varchar(100)

  declare @sql nvarchar(4000)

  create table #temp (starttime datetime)

  -- Using sp_executesql

  set @sql = 'select @DateTime = min(starttime) from ' + @tablename

  execute sp_executesql @sql, N'@DateTime datetime output', @DateTime = @DateTime output

  select @DateTime

  You dont need to create temp table

  ---

  Madhivanan

  Failing to plan is Planning to fail

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  May 8, 2009 at 8:45 am

  #991154

  Madhivanan (5/8/2009)

  ---

  Ramesh (5/8/2009)

  ---

  It can also be done using sp_executesql system procedure

  declare @DateTime DATETIME

  declare @tablename varchar(100)

  declare @sql nvarchar(4000)

  create table #temp (starttime datetime)

  -- Using sp_executesql

  set @sql = 'select @DateTime = min(starttime) from ' + @tablename

  execute sp_executesql @sql, N'@DateTime datetime output', @DateTime = @DateTime output

  select @DateTime

  You dont need to create temp table

  It was just that "copy and paste" is responsible for it..:-D

  --Ramesh
  

  ---