如何从SQL查询中执行SQL查询

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.

我正在使用SQL Server 2014.我有一个名为“Withdraw”的SQL查询，它返回0或-1的值，具体取决于其操作是否成功。

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.

我想要做的是创建第二个查询，它将执行第一个查询并将返回值放在一个变量中，这样我就可以在新查询中的IF语句中使用它。

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

2 个解决方案

#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}

#2

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

您可以将该查询转换为函数，而不是使用PROC

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

#1