How to assign an exec result to a sql variable?

Question

How do you assign the result of an exec call to a variable in SQL? I have a stored proc called up_GetBusinessDay, which returns a single date.

Can you do something like this:

exec @PreviousBusinessDay = dbo.up_GetBusinessDay @Date, -1

Answer 1

I always use the return value to pass back error status. If you need to pass back one value I'd use an output parameter.

Here's a sample stored procedure, with an OUTPUT parameter:

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

Here's an example of calling the stored procedure, with an OUTPUT parameter:

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

Answer 2

This will work if you wish to simply return an integer:

DECLARE @ResultForPos INT 
EXEC @ResultForPos = storedprocedureName 'InputParameter'
SELECT @ResultForPos

Answer 3

declare @EventId int

CREATE TABLE #EventId (EventId int)

insert into #EventId exec rptInputEventId

set @EventId = (select * from #EventId)

drop table #EventId 

Answer 4

From the documentation (assuming that you use SQL-Server):

USE AdventureWorks;
GO
DECLARE @returnstatus nvarchar(15);
SET @returnstatus = NULL;
EXEC @returnstatus = dbo.ufnGetSalesOrderStatusText @Status = 2;
PRINT @returnstatus;
GO

So yes, it should work that way.

Answer 5

I had the same question. While there are good answers here I decided to create a table-valued function. With a table (or scalar) valued function you don't have to change your stored proc. I simply did a select from the table-valued function. Note that the parameter (MyParameter is optional).

CREATE FUNCTION [dbo].[MyDateFunction] 
(@MyParameter varchar(max))
RETURNS TABLE 
AS
RETURN 
(
    --- Query your table or view or whatever and select the results.
    SELECT DateValue FROM MyTable WHERE ID = @MyParameter;
)

To assign to your variable you simply can do something like:

Declare @MyDate datetime;
SET @MyDate = (SELECT DateValue FROM MyDateFunction(@MyParameter));

You can also use a scalar valued function:

CREATE FUNCTION TestDateFunction()  
RETURNS datetime  
BEGIN  
    RETURN (SELECT GetDate());
END

Then you can simply do

Declare @MyDate datetime;
SET @MyDate = (Select dbo.TestDateFunction());
SELECT @MyDate;

Answer 6

Here is solution for dynamic queries.

For example if you have more tables with different suffix:

dbo.SOMETHINGTABLE_ONE, dbo.SOMETHINGTABLE_TWO

Code:

DECLARE @INDEX AS NVARCHAR(20)
DECLARE @CheckVALUE AS NVARCHAR(max) = 'SELECT COUNT(SOMETHING) FROM 
dbo.SOMETHINGTABLE_'+@INDEX+''
DECLARE @tempTable Table (TempVALUE int)
DECLARE @RESULTVAL INT

INSERT INTO @tempTable
    EXEC sp_executesql @CheckVALUE

SET @RESULTVAL = (SELECT * FROM @tempTable)

DELETE @tempTable

SELECT @RESULTVAL 

Answer 7

You can use a Table Variable for that

Code:

DECLARE @PreviousBusinessDay DATETIME
DECLARE @Temp TABLE(BusinessDay DATETIME)
INSERT INTO @Temp EXEC dbo.up_GetBusinessDay @Date, -1
SET @PreviousBusinessDay = (SELECT * FROM @Temp)
SELECT @PreviousBusinessDay

https://www.sqlservertutorial.net/sql-server-user-defined-functions/sql-server-table-variables/