Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2007
    Posts
    7

    Unanswered: Returning Values

    Hi All

    I have a stored procedure that returns a numeric value. I wish to call this procedure from within another one and have the returned value stored against a declared variable. I'm struggling with the syntax!!

    Any help out there?

    Thanks

    Xo

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    how about posting what you have done already?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jul 2007
    Posts
    7
    If I run this in Query Analyser I get a numeric result:

    sp_CreateProcessInstance 'NPD'

    In the calling procedure I've tried using:

    declare @NewID int

    exec @NewID = sp_CreateProcessInstance 'NPD'

    However, the @NewID variable is being set to 0 (although the procedure is firing correctly)

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can we see the definition of sp_CreateProcessInstance please?
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    At the end of your sproc:
    Code:
    RETURN 1 --or 2 or 3 or 4 or 5 or 6 or 7 or 8....
    And don't prefix your sprocs "sp_" *


    *EDIT - unless you know what your doing by naming them like this. Which I suspect you don't given your question.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Dudes, he just needs to change hist stored procedure to a user-defined function. Simple as that...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Probably... but we dun't know for sure, dude

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oh dude, I'm SO sure.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    Oh dude, I'm SO sure.
    Turn this one into a function, dude
    Code:
    CREATE PROC sp_CreateProcessInstance
        @InstanceName VARCHAR(10)
    AS
    
        INSERT INTO ProcessInstance (InstanceName)
        SELECT     @InstanceName
        
        RETURN IDENTITY_INSERT
    
    END

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm SO sure that is not his sproc.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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