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

If could post some code we can help. Otherwise we are just guessing. — Sean Lange
– Sean Lange, Commented Dec 4, 2015 at 14:14
I don't understand what the problem is. What's wrong with your current code? — Tab Alleman
– Tab Alleman, Commented Dec 4, 2015 at 14:21
The line "set @rc = EXECUTE dbo.withdrawl" isn't recognized. EXECUTE isn't recognized as a command at all. Another problem is that dbo.withdrawl isn't found (though other queries do, but I'll find a way to fix that later). — Nimrod Yanai
– Nimrod Yanai, Commented Dec 4, 2015 at 14:22

Tab Alleman · Accepted Answer · 2015-12-04 14:29:53Z

3

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}

answered Dec 4, 2015 at 14:29

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

CHill60 · Accepted Answer · 2015-12-04 14:23:07Z

1

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()

answered Dec 4, 2015 at 14:23

CHill60

2,0711 gold badge14 silver badges22 bronze badges

4 Comments

Nimrod Yanai Over a year ago

Will I still be able to run it as a query and have it do its job though? i.e. will is still be in the "stored procedures" and can it be executed as normal?

Tab Alleman Over a year ago

Conventional wisdom is that functions shouldn't do inserts or updates. They should just return values/data.

Nimrod Yanai Over a year ago

Then while this is helpful general advice, it won't help me, as I still need the procedure to do other things as well. It can help me in another place though, so thanx :D

CHill60 Over a year ago

@TabAlleman - yes I agree. I'd pasted this in before I noticed the code the OP had edited - although the "Withdraw" should have given me a clue!

Collectives™ on Stack Overflow

How to execute SQL query from within an SQL query

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related