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
This question is related to
sql
sql-server
tsql
stored-procedures
declare @EventId int
CREATE TABLE #EventId (EventId int)
insert into #EventId exec rptInputEventId
set @EventId = (select * from #EventId)
drop table #EventId
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;
This will work if you wish to simply return an integer:
DECLARE @ResultForPos INT
EXEC @ResultForPos = storedprocedureName 'InputParameter'
SELECT @ResultForPos
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
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.
Source: Stackoverflow.com