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
Add a comment
|
7 Answers
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
2 Comments
KM.
By using an OUTPUT parameter, you can return back any data type, the RETURN value from a stored procedure can only be an integer.
Morvael
Just a side note, OUTPUT parameters that are declared with a value don't need to be passed in. This means that if you are altering an existing SP you can do it safely without risking breaking anything. eg ,@Param3 datetime = '1900-01-01' OUTPUT.
This will work if you wish to simply return an integer:
DECLARE @ResultForPos INT
EXEC @ResultForPos = storedprocedureName 'InputParameter'
SELECT @ResultForPos
7 Comments
Code Maverick
-1 This will only return an integer. The OP wants to return a date. The accepted answer by @KM. is the correct answer, as it uses OUTPUT instead of RETURN.
Lzh
Actually this works. The example as to how to get an integer that is returned, you can do the same for all other kinds (didn't check if table possible, but I think yes.) I just tried it for nvarchar(50).
James
@Mzn "you can do the same for all other kinds", certainly doesn't work with
UNIQUEIDENTIFIER.Lzh
@James Why? What's different about the uniqueidentifier datatype? Stored procedures cannot return uniqueidentifiers?
|
declare @EventId int
CREATE TABLE #EventId (EventId int)
insert into #EventId exec rptInputEventId
set @EventId = (select * from #EventId)
drop table #EventId
5 Comments
Michael Sander
actually the only working way decribed here apart from changing the stored proc's signature
Jeff Beagley
Used this as well for a date, when the underlying Sproc doesn't have an output parameter either. (Underlying sproc had another Exec inside it from dynamic SQL)
SQL Police
@MichaelSander Totally right, all other solutions are not correctly answering the OPs question. The only way is a temporary table which holds the results.
DLeh
Only way that works here without requiring edits to the proc, which I can't do in my case. +1
error
You can also use a table variable in place of a temp table.
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.
2 Comments
KM.
in the OP's example they want to return a date, Stored procedures can only RETURN an integer value to a calling procedure or an application.
youcantryreachingme
This is incorrect. That documentation is talking about a scalar function. The OP is asking about a stored procedure with its resultset. @KM. note even the source documentation is assigning the returned value to nvarchar(15), not int.
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;
Comments
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
Comments
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/
