Variable in dynamic SQL reused

• covitz

  Old Hand

  Points: 322

  More actions

  December 16, 2003 at 9:40 am

  #160306

  Hi All

  Anyone have any experience - defining a variable - using the variable in a dynamic environment - and then reusing the variable outside the dynamic environment. For example:

  declare @total_rows numeric(12,0),@percent numeric(10,2)

  select @sqlstr='

  select @total_rows=count(*) from '+sometable''

  sp_executesql @sqlstr

  select @percent=(10/@total_rows)*100

  Thanks in advance

• jxflagg

  Hall of Fame

  Points: 3817

  More actions

  December 16, 2003 at 10:14 am

  #486442

  use sp_executesql with an output parameter, like this:

  declare @sqlstr nvarchar(4000)

  declare @params nvarchar(4000)

  declare @total_rows numeric(12,0)

  declare @percent numeric(10,2)

  select @sqlstr = 'select @total_rows=count(*) from master.dbo.sysobjects'

  select @params = '@total_rows varchar(255) output'

  EXECUTE sp_executesql @sqlstr, @params, @total_rows output

  select @percent=(10/@total_rows)*100

  select @percent

• covitz

  Old Hand

  Points: 322

  More actions

  December 16, 2003 at 10:26 am

  #486447

  Thanks JXFLAGG. The OUTPUT parameter worked well.