CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Thanushka
Thanushka

Reputation: 1445

SQL Server execute SP within SP and use results of internal SP call

I need to execute a stored procedure within another stored procedure and take results of the internal stored procedure to use in the external stored procedure.

Simply like follows.

SP1

CREATE PROCEDURE spExternal
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @intInternalResult INT

    SET intInternalResult= EXEC spInternal
END

SP Internal

CREATE PROCEDURE spInternal 
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 1+2
END

Upvotes: 0

Views: 840

Answers (2)

podiluska
podiluska

Reputation: 51504

If your stored procedure is only returning a single integer value, use RETURN

create proc pInternal
as
begin
    return 2
end

then you can access the result as

exec @intInternalResult = pInternal

If you are returning non integer or multiple values, use OUTPUT parameters

Upvotes: 0

G.S
G.S

Reputation: 777

My Friend, You can not assign value to a variable which you are not returning. You can write a function to do job of stored procedure spInternal which will return a value. Else you can do it this way:

CREATE PROCEDURE spInternal 
AS
BEGIN

    SELECT 1+2
END

CREATE PROCEDURE spExternal
AS
BEGIN

    DECLARE @intInternalResult INT
    CREATE TABLE #temp
    (
        Rslt int
    )
    INSERT INTO #temp (Rslt)
    EXEC spInternal

    SELECT @intInternalResult = Rslt
    FROM #temp
    DROP TABLE #temp
    PRINT @intInternalResult
END

EXEC spExternal

And one more thing, next time don't forget to put @ in front of a variable.

Upvotes: 1

Related Questions