SQL CALL A SP FROM ANOTHER SP

Hugh Jackman

Total Post:51

Points:359
Posted by  Hugh Jackman
 1766  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

      Modified On Mar-30-2018 05:35:51 AM

Answer

NEWSLETTER

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