SQL CALL A SP FROM ANOTHER SP

Hugh Jackman

Total Post:52

Points:366
Posted by  Hugh Jackman
 1541  View(s)
Ratings:
Rate this:
HI developers!

Can I please have some help with the syntax of a SP in SQL.

Here is my code:

CREATE PROCEDURE usp_GetValue

(

    @ID VARCHAR(10),

    @Description VARCHAR(10)

)

AS

BEGIN

    return @ID + @Description

END

CREATE PROCEDURE usp_InsertValue

(

    @ID VARCHAR(10),

    @FirstName VARCHAR(50),

    @LastName VARCHAR(50),

    @Description VARCHAR(10),

    @Comment VARCHAR(max)

)

AS

BEGIN

    Declare @v_Value VARCHAR(15)

    Set @v_Value = usp_GetValue(@ID, @Description)

END

In the usp_InsertValue SP, I am wanting to declare and set a variable. Once the variable has been declared, I then wish to call another SP with parameters to set the value of the declared variable.

I am not sure of the syntax. May I please have some help?

  1. shreesh chandra shukla

    Post:105

    Points:735
    Re: SQL call a SP from another SP

    Hell Mr. hugh!

    you shuld to try this

    I think output parameters should be the proper way: See this post: stored procedure returns varchar

    CREATE PROCEDURE usp_GetValue

    (

        @ID VARCHAR(10),

        @Description VARCHAR(10),

        @res  VARCHAR(10) OUTPUT

    )

    AS

    BEGIN

        return @ID + @Description

    END

    CREATE PROCEDURE usp_InsertValue

    (

        @ID VARCHAR(10),

        @FirstName VARCHAR(50),

        @LastName VARCHAR(50),

        @Description VARCHAR(10),

        @Comment VARCHAR(max)

    )

    AS

    BEGIN

        Declare @v_Value VARCHAR(15)

        EXEC usp_GetValue @ID, @Description, @v_Value

        -- use @v_Value here...

    END

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!