How to get the result of the query executed from within an EXEC??

• FelixG

  SSCrazy

  Points: 2550

  More actions

  March 16, 2005 at 9:46 am

  #66987

  Hello,

  I'll try to make clear my problem through a eaxmple:

  declare @MyVar integer

  select @MyVar= count(*) from MyTable where MyCondition

  This will put the result into @MyVar, that's all right.

  Let's think now, the name of the table  is variable (a parameter).

  declare @sql varchar(1000)

  declare @TableName varchar(30)

  set @sql= 'Update '+@TableName +' set MyField= 0'

  exec(@SQL)

  This is all right too....

  Now I want a combination of the two previous cases: to count the records matching some conditions from one table whose name comes into a parameter and put the result into a variable.

  I took the following workaround:

  declare @sql varchar(1000), @TableName varchar(30), @MyVar Int

  set @sql= 'Select count(*) as Quantity INTO ##MyTemp FROM '+@TableName +' where MyCondition'

  exec(@SQL)

  Select @MyVar= Quantity from ##MyTemp

  Is there any way of achieving this without using temp tables???

  TIA,

  Felix

• PW-201837

  SSC-Insane

  Points: 20805

  More actions

  March 16, 2005 at 9:52 am

  #547306

  Use sp_executesql

  declare @sql nvarchar(1000)

  declare @result int

  set @sql = N'select @C = count(*) from ' + @TableName

  exec sp_executesql @sql, N'@c int output', @C = @result output

  print @result

• FelixG

  SSCrazy

  Points: 2550

  More actions

  March 16, 2005 at 10:08 am

  #547309

  That works great, Thanks a LOT!!!