How to Assign an Exec Result to a SQL Variable

How to assign an exec result to a sql variable?

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.

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

call to 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

how to assign results from exec to variable

Use an output param:

declare @sql nvarchar(500)
declare @xml XML

set @sql = 'set @xml = (SELECT ..... FOR XML PATH(''ProductDistributionBrandDetail''),ROOT(''ProductDistributionBrandDetails''),TYPE)'    

EXEC sp_executesql @sql, N'@xml XML output', @xml = @xml OUTPUT

select @xml

How to set value to variable using 'execute' in t-sql?

You can use output parameters with sp_executesql.

DECLARE @dbName nvarchar(128) = 'myDb'
DECLARE @siteId int 
DECLARE @SQL nvarchar(max) = N'SELECT TOP 1 @outputFromExec = Id FROM ' + quotename(@dbName) + N'..myTbl'
exec sp_executesql @SQL, N'@outputFromExec int out', @siteId out
select @siteId

set @var = exec stored_procedure

You can use sp_executesql instead of exec to assign to scalar output parameters

DECLARE @out int

EXEC sp_executesql N'select @out_param=10',
                   N'@out_param int OUTPUT',
                     @out_param=@out OUTPUT

SELECT @out

For exec I'm only aware of how to do it using a table variable

declare @out table
(
out int
)

insert into @out
exec('select 10')

select * 
from @out

For stored procedures you would also use an output parameter or a return code. The latter can return a single integer only and generally preferred for returning error codes rather than data. Both techniques are demonstrated below.

create proc #foo 
@out int output
as
set @out = 100
return 99

go

declare @out int, @return int

exec @return = #foo @out output

select @return as [@return], @out as [@out]

drop proc #foo

How to store the result of exec in a variable

DECLARE @myVar VARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)

IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1
CREATE TABLE #t1 (col1 INT, col2 INT)
INSERT INTO #t1
SELECT 1, 1
UNION
SELECT 1, 2

SET @SQL = 'SET @myVar = (SELECT * FROM #t1 AS T FOR JSON AUTO);' 
EXEC sp_executesql @SQL, N'@myVar VARCHAR(MAX) OUT', @myVar OUT

SELECT @myVar

Assign result of dynamic sql to variable

You can use sp_executesql with output parameter.

declare @S nvarchar(max) = 'select @x = 1'

declare @xx int
set @xx = 0

exec sp_executesql @S, N'@x int out', @xx out

select @xx

Result:

(No column name)
1

Edit

In my sample @S is instead of your @template. As you can see I assign a value to @x so you need to modify @template so it internally assigns the comma separated string to the variable you define in your second argument to sp_executesql. In my sample N'@x int out'. You probably want a varchar(max) output parameter. Something like N'@Result varchar(max) out'

Here is another example building a comma separated string from master..spt_values

declare @template nvarchar(max)
set @template = 
'select @Result += cast(number as varchar(10))+'',''
from master..spt_values
where type = ''P''
'

declare @CommaString varchar(max)
set @CommaString = ''

exec sp_executesql @template, N'@Result varchar(max) out', @CommaString out

select @CommaString

How to store Exec result to datetime variable where exec result has variable table name?

Try following way. Execute query result store into the table instead of assign to a variable.

Declare @F VARCHAR(50) = (select replace ('H-10','-',''));
DECLARE @SQLQuery AS NVARCHAR(500)
set @SQLQuery=
N'SELECT
   top 1 DATEADD(MINUTE, -330, time_stamp) as time
 FROM
  DMD_'+@F+'_DC_data
 ORDER BY
  time_stamp ASC';

DECLARE @TempTable TABLE (SOR_time datetime)
insert @TempTable 
exec (@SQLQuery)
select * from @TempTable

How to assign a variable to the execution of a stored procedure?

You need a RETURN at the bottom of your procedure.

RETURN @NewEventID

Here is a complete but simple example:

CREATE PROCEDURE [dbo].[uspExampleOne] 
@Parameter1 INT
AS

BEGIN

SET NOCOUNT ON

RETURN 333

SET NOCOUNT OFF

END
GO

and

Declare @MyValue INT
EXEC @MyValue = [dbo].[uspExampleOne]  111
SELECT '@MyValueHere' = @MyValue 

Result:

@MyValueHere
333

But a better design IMHO is to use an OUTPUT variable:

Why?

What happens when you need a second OUTPUT? What happens when the needed value is not an INT?

ALTER PROCEDURE [dbo].[uspExampleOne] 
@Parameter1 INT ,
@OutParameter2 INT OUT
AS

BEGIN

SET NOCOUNT ON

Select @OutParameter2 = 444

RETURN 333

SET NOCOUNT OFF

END
GO

and

Declare @MyValue INT
Declare @OutParameterTwo INT

EXEC @MyValue = [dbo].[uspExampleOne]  111 , @OutParameterTwo OUT
SELECT '@MyValueHere' = @MyValue 

Select '@OutParameterTwoHere' = @OutParameterTwo

Output

@MyValueHere
333
@OutParameterTwoHere
444

Below shows what I mean about "future proofing" with OUTPUT parameters

ALTER PROCEDURE [dbo].[uspExampleOne] 
@Parameter1 INT ,
@OutParameter2 INT OUT,
@OutParameter3 VARCHAR(128) OUT
AS

BEGIN

SET NOCOUNT ON

Select @OutParameter2 = 444
Select @OutParameter3 = 'Better Design With Output Parameters.  Not stuck with 1 return-value or data-type'

RETURN 0 /* everything went ok */

SET NOCOUNT OFF

END
GO

and the call to it

Declare @MyValue INT
Declare @OutParameterTwo INT
Declare @OutParameterThree VARCHAR(128)

EXEC @MyValue = [dbo].[uspExampleOne]  111 , @OutParameterTwo OUT , @OutParameterThree OUT
SELECT '@MyValueHere' = @MyValue 

Select '@OutParameterTwoHere' = @OutParameterTwo , '@OutParameterThreeHere' = @OutParameterThree

and output

@OutParameterTwoHere    @OutParameterThreeHere
444                     Better Design With Output Parameters.  Not stuck with 1 return-value or data-type

---

Related Topics