CODEWITHSUNDEEP
sql-server-2005
AMH
AMH

Reputation: 6451

return two output parameters sp_executesql

I have dynamic query I want to get two output parameters from it I used the following code , but the output parameters return null 

declare @query nvarchar(max);
declare @result int; 
declare @type int
declare @mainVarcharLength int; 

set @query = 'select   count(*) ,  Type_Code from Customers   WHERE Customers.Cust_Name =  ''CUSTOMER 99''  '
set @query = @query + '  and Cus_Is_Active = 1  Group BY   Type_Code';
select  @query

EXEC sp_executesql @query, N'@result int OUTPUT,  @type int OUTPUT', @result,  @type

select @result
select @type

How to solve that , and how to pass multiple output parameters

Upvotes: 13

Views: 22873

Answers (1)

Alex K.
Alex K.

Reputation: 175766

You need to state what is being allocated to the outputs;

set @query = 'select @result=count(*), @type=Type_Code from Customers ....'

then decorate the outputs with OUTPUT;

EXEC sp_executesql @query,   
    N'@result int OUTPUT, @type int OUTPUT',
    @result OUTPUT, 
    @type OUTPUT

(You could also pass ''CUSTOMER 99'' as an input)

Upvotes: 20

Related Questions