none
Stored Procedure with OUTPUT parameter RRS feed

  • Question

  • Experts,

    I've sample SP as below :-

    ALTER PROCEDURE [dbo].[uspDedups]
    	@CES	VARCHAR(8000)	OUTPUT
    AS
    
    DECLARE	@CreateExcelSheets		VARCHAR(8000)	= 'Am'
    
    SELECT	@CES = @CreateExcelSheets
    
    RETURN
    
    GO
    DECLARE	@CES1	VARCHAR(8000)
    EXEC [dbo].[uspDedups] @CES = @CES1 OUTPUT

    Can anybody please help why I'm not able to get actual value back with the execute statement?

    What am I missing here?

    Thanks

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS

    Monday, November 4, 2013 4:11 PM

Answers

All replies

  • Your execute statement is the problem.  You are assigning the output parameter to the value of the variable you declared.  Try the following:

    DECLARE	@CES1	VARCHAR(8000)
    EXEC [dbo].[uspDedups] @CES1 = @CES OUTPUT


    Monday, November 4, 2013 4:19 PM
  • It works correctly. You need a final SELECT.

    CREATE PROCEDURE [dbo].[uspDedups]
    	@CES	VARCHAR(8000)	OUTPUT
    AS
    
    DECLARE	@CreateExcelSheets		VARCHAR(8000)	= 'SQLUSA'
    
    SELECT	@CES = @CreateExcelSheets
    
    RETURN
    
    GO
    DECLARE	@CES1	VARCHAR(8000)
    EXEC [dbo].[uspDedups] @CES = @CES1 OUTPUT
    SELECT @CES1;
    -- SQLUSA


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    • Marked as answer by gk1393 Monday, November 4, 2013 4:38 PM
    Monday, November 4, 2013 4:21 PM
    Moderator
  • I don't see anything wrong with that.  When I run

    ALTER PROCEDURE [dbo].[uspDedups]
    	@CES	VARCHAR(8000)	OUTPUT
    AS
    
    DECLARE	@CreateExcelSheets		VARCHAR(8000)	= 'Am'
    
    SELECT	@CES = @CreateExcelSheets
    
    RETURN
    
    GO
    
    DECLARE	@CES1	VARCHAR(8000)
    EXEC [dbo].[uspDedups] @CES = @CES1 OUTPUT
    SELECT @CES1
    go
    

    the result of the final SELECT is 'Am', as you would expect.

    Tom

    Monday, November 4, 2013 4:24 PM