How to execute SQL query from within an SQL query

Question

I am using SQL Server 2014. I have a SQL query called "Withdraw" which returns a value of either 0 or -1, depending on the success of its operation.

What I want to do is create a second query which will execute the first one and place the return value in a variable so I can use it in an IF statement within the new query.

The simple way to do this will be to copy the code of the other query, which can be done (it's not that long of a query), but I was wondering if there is a more elegant way to do this by executing the query instead of copying it.

Thanks guys!

Withdraw query:

PROC [dbo].[withdrawl]
    (@AccountNum AS INT,
     @Amount AS INT)
AS
BEGIN
    set nocount on

    declare @rc as integer

    begin transaction
    begin try 
        IF @Amount < 20000
        BEGIN
            IF @Amount < ((SELECT crntbalance FROM tblAccounts WHERE acctNum = @AccountNum) + (SELECT overdraftsz FROM tblAccounts WHERE acctNum = @AccountNum))
            BEGIN
                UPDATE tblAccounts
                SET crntbalance -= @Amount
                WHERE acctNum = @AccountNum

                INSERT INTO tblTransactions (actNum, trnTypCod, amount) 
                VALUES (@AccountNum, 1, @Amount)

                UPDATE tblAccounts
                SET crntbalance += -5
                WHERE acctNum = @AccountNum

                INSERT INTO tblTransactions (actNum, trnTypCod, amount) 
                VALUES (@AccountNum, 5, 5)

                SET @rc = 0
            END
            ELSE
            BEGIN
                PRINT 'You do not have sufficient funds in your account to make this withdrawl.'
                SET @rc = -1
            END
        END
        ELSE
        BEGIN
            IF @Amount < ((SELECT crntbalance FROM tblAccounts WHERE acctNum = @AccountNum) + (SELECT overdraftsz FROM tblAccounts WHERE acctNum = @AccountNum))
            BEGIN
                UPDATE tblAccounts
                SET crntbalance -= @Amount
                WHERE acctNum = @AccountNum

                INSERT INTO tblTransactions (actNum, trnTypCod, amount) 
                VALUES (@AccountNum, 1, @Amount)

                SET @rc = 0
            END
            ELSE
            BEGIN
                PRINT 'You do not have sufficient funds in your account to make this withdrawl.'
                SET @rc = -1
            END
        END

        commit transaction
    END TRY
    BEGIN CATCH
        rollback transaction
        SET @rc = -1
    END CATCH

    BEGIN
        SELECT
            crntbalance AS 'New Balance'
        FROM 
            tblAccounts 
        WHERE 
            acctNum = @AccountNum
    END

    return @rc
END

New query:

CREATE PROCEDURE Transfer 
     (@TakeAccount AS INT, @GiveAccount as INT, @amount as INT)
AS
BEGIN
    declare @rc as integer
    BEGIN TRY
        set @rc = EXECUTE dbo.withdrawl
        IF @rc = -1
            BEGIN

            END
        ELSE
            BEGIN
            END
    END TRY
    BEGIN CATCH
    END CATCH
END

Answer 1

This is the correct syntax for setting a variable with the return value of a stored proc (assuming the variable is already declared):

EXEC @rc = [schema].[StoredProc]
  {Parameters, if any}

Answer 2

Instead of using a PROC you can convert that query to a function

CREATE FUNCTION [dbo].[Withdraw] 
(
    -- insert parameters here
)
RETURNS int
AS
BEGIN

    DECLARE @Result int

    -- insert processing here
    set @Result = -1


     return @Result
END

The results of which you can then use in your other query

SET @rc =  dbo.Withdraw()