Output Parameter in SP

• beath

  SSCrazy

  Points: 2939

  More actions

  August 17, 2004 at 6:07 am

  #108970

  If I have a very simple stored procedure with an output paramter as follows:

  CREATE PROCEDURE SP1 @i varchar(10) output AS

  set @i = 'Success'

  GO

  Now if I execute the follwing code:

  declare @var varchar(10)

  exec SP1 @i = @var output

  select @var

  I get the value 'Success' returned as I would expect. But if I try and build up the command through a sql string as follows:

  declare @var varchar(10)

  declare @cmd varchar(100)

  set @cmd = 'exec SP1  @i = ' + @var + ' output'

  exec(@cmd)

  select @var

  I get NULL returned. I need to be able to use the EXEC(@cmd) syntax but can't get the output paramter returned.

  Thanks for any help.....

• centexbi

  SSC Enthusiast

  Points: 125

  More actions

  August 17, 2004 at 10:45 am

  #519397

  When you use exec(@cmd), you are running a different session and the scope of the variable @var is the current session only. It will always return NULL.

  What are you trying to achive by using the exec()?

• Nick Caramello

  Grasshopper

  Points: 12

  More actions

  August 17, 2004 at 10:53 am

  #519399

  You can use sp_executeSQL with an output parameter in much the same way that you are trying to use exec.  It is likely a preferable method anyway.

• bill /3enac

  Newbie

  Points: 2

  More actions

  August 17, 2004 at 12:35 pm

  #519419

  I have a similar problem. I would articulate the problem a bit differently: I'm struggling to have a stored proc execute dynamic sql, then assign its results to an output variable.

  Here's the code that won't even run for me.

  create proc dbo.getUUIDfromDB(

  @objID int,

  @myUUID varchar(255) output

  ) as

   declare @sql varchar(1000)

   select @sql = 'select myVal from myTable where objID = '  + @objID

   select @myUUID=exec(@sql)

   -- also tried this:

   -- select exec(@sql) into @myUUID

   print 'myUUID is ' + @myUUID

  go

  Thanks for any help through examples for fixing this.

• paulmazur

  SSC Enthusiast

  Points: 172

  More actions

  August 18, 2004 at 8:27 am

  #519591

  Try this:

   

   

  CREATE PROC dbo.getUUIDfromDB

    @objID int,

    @myUUID varchar(255) OUTPUT

  AS

  DECLARE @sql nvarchar(1000)

  DECLARE @ParmDef nvarchar(500)

  SET @ParmDef = N'@Parm_objID int, @Parm_myVal varchar(255) OUT'

  SET @sql = N'SELECT @Parm_myVal = myVal FROM myTable WHERE objID = @Parm_objID'

  EXEC sp_executesql @sql,

                     @ParmDef,

                     @Parm_myVal = @myUUID OUT,

                     @Parm_objID = @objID

  PRINT 'myUUID is ' + @myUUID

  GO

• bill /3enac

  Newbie

  Points: 2

  More actions

  August 19, 2004 at 12:04 pm

  #519858

  Thanks so much, Paul. That was a great example, and it allowed me to get to my solution.

  Bill