Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How do I pass the param RETURN from previous query to next another query in stored procedure.

Answered How do I pass the param RETURN from previous query to next another query in stored procedure.

  • Wednesday, February 13, 2013 10:37 PM
     
     
    Sign In to Vote
    0

     Hi all,

    I created a T-SQL store procedure with several queries as folllowing: 

       

    CREATE PROCEDURE [dbo].[Device]

        (@DeviceType bigint,
        @DeviceVersion varchar(50),
        @DTSTAMP datetime,
        @DeviceID bigint OUT)

    )
      AS

            BEGIN
      
     SELECT @DeviceID = ID
                      FROM RPT.Device d
              WHERE d.DeviceTypeCLID = @DeviceType AND d.DeviceVersion = @DeviceVersion
                      RETURN 
            END         

     -- Now I would like to use the result of RETURN for OUT parameter for next query.

            INSERT INTO Customer (ID, DeviceID ) 
                   VALUES (@CustomerID, @DeviceID)       

    Please help me for the solution. Is it in above @DeviceID bigint OUT IN) ??? Thanks in advance.

            
    • Edited by avt2k13 Wednesday, February 13, 2013 10:49 PM
    • Edited by avt2k13 Wednesday, February 13, 2013 10:50 PM
    •  
     

All Replies

  • Wednesday, February 13, 2013 11:00 PM
     
     Answered
    Sign In to Vote
    0

    The question is not fully clear to me. Is the INSERT statement intended to be part of the stored procedure? In such case you need to take out the RETURN statement to reach the INSERT statement?

    Or do you intend to call the procedure and then insert? In such case, you would say:

       EXEC dbo.Device @DeviceType, @DeviceVersion, @Dstamp, @DeviceID OUTPUT
       INSERT INTO Customer (ID, DeviceID ) 
                   VALUES (@CustomerID, @DeviceID)  

    Note that you must include the OUTPUT keyword in the call as well.

    The RETURN statement is customary only used to return success/failure, but with TRY/CATCH return values are not used much these days.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Thursday, February 14, 2013 4:08 AM
     
     
    Sign In to Vote
    0

    Hi,

    The INSERT statement is apart of above stored procedure, even though there is an output parameter because I need it for input parameter of another stored procedure. Hope it clear. Any further help is much appreciated.

     
  • Thursday, February 14, 2013 6:59 AM
     
     
    Sign In to Vote
    1

    No issues with the proc or query. Use Erland's suggestion. The @DeviceID will have the return value. You can use the wherever you want.

    EXEC dbo.Device @DeviceType, @DeviceVersion, @Dstamp, @DeviceID OUTPUT

    Exec dbo.OtherPRoc @DeviceID --@DeviceID is input for OtherPRoc.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked As Answer by avt2k13 Thursday, February 14, 2013 2:37 PM
    • Unmarked As Answer by avt2k13 Friday, February 15, 2013 2:25 PM
    •  
     
  • Thursday, February 14, 2013 8:56 AM
     
     
    Sign In to Vote
    1

    The INSERT statement is apart of above stored procedure, even though there is an output parameter because I need it for input parameter of another stored procedure. Hope it clear. Any further help is much appreciated.

    Remove the RETURN statement. Else you will not reach the INSERT statement.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Friday, February 15, 2013 2:24 PM
     
     
    Sign In to Vote
    0

     Thanks.                                             






    • Edited by avt2k13 Friday, February 15, 2013 2:29 PM
    • Edited by avt2k13 Friday, February 15, 2013 2:34 PM
    • Edited by avt2k13 Friday, February 15, 2013 8:20 PM
    • Edited by avt2k13 Monday, February 18, 2013 6:02 AM
    • Edited by avt2k13 Monday, February 18, 2013 6:09 AM
    • Edited by avt2k13 Tuesday, February 19, 2013 5:30 AM
    •