How to assign result of a EXEC into a local variable

• Takauma

  Say Hey Kid

  Points: 697

  More actions

  July 3, 2007 at 6:15 pm

  #179679

  Here's a issue related to another thread of mine:  How to assign result of a EXEC into a local variable.

  begin

  declare @stuff integer , @sql varchar(max)

  set

  @sql = 'select @stuff = 5 '

  exec

  (@sql)

  -- check and see

  select

  @stuff

  end

   

  Result:  Error msg

  Msg 137, Level 15, State 1, Line 1

  Must declare the scalar variable "@stuff".

  Takauma

• michaela

  SSCertifiable

  Points: 5649

  More actions

  July 3, 2007 at 7:11 pm

  #717037

  Fisrt step:

  CREATE

  PROCEDURE dbo.spOutputTest

  @stuff

  int OUTPUT

  AS

  begin

  set

  @stuff = 5

  end

  Second:

  DECLARE

  @stuff int

  EXEC

  dbo.spOutputTest @stuff OUTPUT

  SELECT

  @stuff AS YourStuff

   

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  July 4, 2007 at 1:06 am

  #717068

  look up sp_executesql

  declare

  @stuff int , @sql nvarchar(1000)

  EXEC

  sp_executesql N'select @inner = 5', N'@inner int OUTPUT', @inner = @stuff OUTPUT

  -- check and see

  select

  @stuff

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass