Storing Stored Procedure OUT PUT in Global Variable

• vsv

  Mr or Mrs. 500

  Points: 578

  More actions

  November 1, 2005 at 2:02 am

  #66832

  Hi,

  I have created a DTS Package in which I am using SQL Task to execute a Stored procedure. Which has an out put parameter. I need to get the value of the out put value into a Global Variable.

  But I am confused as to how to do this

  Can any one help me

  Thanks 'n Regards

  Sajan

• Anatol Romanov

  Old Hand

  Points: 349

  More actions

  November 1, 2005 at 9:58 pm

  #601734

  This is easy to do, see the example below.

  1. Createthe stored procedure

  CREATE PROOC procFitbitsDotComDotAu @Msg VARCHAR(50) OUTPUT

  AS

  SET @Msg = 'Fitbits.com.au - Australian SQL Server specialist'

  RETURN 123

  GO

  2. Create DTS package with 2 global variables gvRC (integer) and gvMsg (string)

  3. Create an Execute SQL Task with the following TSQL script:

  SET NOCOUNT ON

  DECLARE @rc INT

  DECLARE @Msg VARCHAR(500)

  EXEC @rc = procFitbitsDotComDotAu @Msg OUTPUT

  SELECT @rc,  @Msg

  Specify output parameters as "Row Value" with bind variables gvRC and gvMsg.

  4. Run the task and inspect global variables - they should have output values assigned within the stored procedure.

  Hope this helps,

  Anatol Romanov

  http://www.fitbits.com.au