Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149

    Unhappy Unanswered: SP: Default value for Output parameters

    When I call a proc with one input parameter and two output parameters, (all the three parameters having defaults in the proc), I was expecting to see these values defaulted to in the proc. But apparently, this not the case. Could someone tell me what am I doing wrong here? Appreciate your time.

    USE Pubs
    GO

    CREATE PROCEDURE dbo.MyTestProc
    (@InputParam VARCHAR(30) = 'Input',
    @OutPutParam1 VARCHAR(30) = 'OutputParam1' OUTPUT,
    @OutPutParam2 VARCHAR(30) = 'OuputParam2' OUTPUT)
    AS
    BEGIN
    SELECT @InputParam, @OutPutParam1, @OutPutParam2
    END
    GO

    -- Call to the proc
    USE Pubs
    GO

    DECLARE @I1 VARCHAR(30)
    DECLARE @P1 VARCHAR(30)
    DECLARE @P2 VARCHAR(30)

    SET @I1 = 'PassedInput'


    EXEC dbo.MyTestProc
    @I1,
    @P1 OUTPUT,
    @P2 OUTPUT

    SELECT @I1, @P1, @P2

    EXEC dbo.MyTestProc

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You're over-riding the values with Null

    Try this

    DECLARE @I1 VARCHAR(30)
    DECLARE @P1 VARCHAR(30)
    DECLARE @P2 VARCHAR(30)

    SET @I1 = 'PassedInput'


    EXEC dbo.MyTestProc
    @I1
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    Brett-

    Thanks for your reply. I see that. Is it accurate to say, if I want to get the default values into my calling program's variables, it can be done only by initializing the output parameters in the proc instead of using default values in the signature of the proc?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Either inside or out

    Code:
    DECLARE @I1 VARCHAR(30)
    DECLARE @P1 VARCHAR(30)
    DECLARE @P2 VARCHAR(30)
    
    SELECT @I1 = 'PassedInput', @P1 = 'OutputParam1', @P2 = 'OutputParam2'
    
    
    EXEC dbo.MyTestProc 
    @I1,
    @P1 OUTPUT,
    @P2 OUTPUT
    
    SELECT @I1, @P1, @P2
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •