How Can I Pass Values Between SQL Code and Stored Procedures?

Passing Values Between SQL Code and Stored Procedures

When working with SQL, it's sometimes necessary to assign the result of an exec call to a variable. In this scenario, you want to assign the output of up_GetBusinessDay to a variable called @PreviousBusinessDay.

Limitations of Directly Assigning Exec Results

While it may seem logical to assign the exec result directly to a variable, this approach is not supported in SQL. The reason for this is that the exec statement is used to execute stored procedures without returning a value. Therefore, you can't directly assign the exec result to a variable.

Using Output Parameters

To pass a single value back from a stored procedure, you can use an output parameter. An output parameter allows you to specify a variable that will receive the value returned by the stored procedure. Here's how you would create and use an output parameter in SQL Server:

Stored Procedure Definition:

CREATE PROCEDURE YourStoredProcedure 
(
    @Param1 int
   ,@Param2 varchar(5)
   ,@Param3 datetime OUTPUT
)
AS
    IF ISNULL(@Param1, 0) > 5
    BEGIN
        SET @Param3 = GETDATE()
    END
    ELSE
    BEGIN
        SET @Param3 = '1/1/2010'
    END
    
    RETURN 0
GO

Stored Procedure Call:

DECLARE @OutputParameter datetime
       ,@ReturnValue     int

EXEC @ReturnValue = YourStoredProcedure 1, NULL, @OutputParameter OUTPUT

PRINT @ReturnValue
PRINT CONVERT(char(23), @OutputParameter, 121)

Output:

0
2010-01-01 00:00:00.000

By using an output parameter, you can effectively assign the result of an exec call to a variable. This technique is commonly used when you need to retrieve a single value from a stored procedure.

The above is the detailed content of How Can I Pass Values Between SQL Code and Stored Procedures?. For more information, please follow other related articles on the PHP Chinese website!