can you set a local var equal to the output of a dynamic sql call?

• BaldingLoopMan

  SSCertifiable

  Points: 6378

  More actions

  June 10, 2010 at 8:37 am

  #222006

  declare @sql varchar(1000)

  declare @testInt int

  set @testInt =0

  set @sql = 'select 1'

  exec (@sql)

  --how can i set @testInt = to what the exec(@sql) returns?

• Dan.Humphries

  SSChampion

  Points: 12125

  More actions

  June 10, 2010 at 8:41 am

  #1178652

  as long as the sql statement returns a single value alone then you can do

  @variablename= select 1

  or

  @variablename= exec(@sql)

  again select statement can return one and only 1 value.

  Dan

  If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

• BaldingLoopMan

  SSCertifiable

  Points: 6378

  More actions

  June 10, 2010 at 8:48 am

  #1178655

  yea, that doesn't work. I've got the solution though if you want ot see it.

  declare @sql nvarchar(1000)

  declare @testInt int

  declare @ParmDefinition nvarchar(100)

  --set @testInt =0

  set @sql = 'select @testInt =1'

  set @ParmDefinition = '@testInt int out'

  exec sp_executesql @sql,@ParmDefinition,@testInt out

  select @testInt