CODEWITHSUNDEEP
sql-serverstored-procedures
Mr.J
Mr.J

Reputation: 440

How To retrieve the result of a Stored Procedure using OUTPUT parameter

I would Like to ask for your assistance regarding this matter.

I need a variable that would update a column on one of my tables.

The first problem that I have encountered is I need to retrieve the data from series of columns, checking if these columns are numeric or not.

To solve this, I used TSQL and it works great. Now here is where I seem to hit a dead end. I need to retrieve the result of this procedure. I tried converting it to a function, but as per many trials (and some google searches) It seems TSQL cannot be used in a function as stated here so I am sticking to Stored Procedures, but how do I retrieve the result? I tried to use OUTPUT parameters, but I get hit with this error 

The formal parameter "@R" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.

Even though I declared R as a output parameter, I also declared @R to output the result in my sp_executesql transaction, but I still get the error, May I ask what am I doing wrong?

Please, the Stored procedure is working fine, I just need the output. Thank you.

ALTER procedure [dbo].[SaveRinHead]


@SumNo as nvarchar(15)
,@R as decimal(18,3) output

as

declare @cursor CURSOR
declare @colname as integer
declare @top as integer
declare @query as nvarchar(MAX)
declare @TSQL as nvarchar(MAX) 
declare @topass as nvarchar(MAX) = ''
declare @DimItem as nvarchar(10)

set @DimItem = (select distinct dimitem from SumNo)

SET @cursor = CURSOR FOR 
(select cast([Name] as decimal(18,0)) from sys.columns where object_id in (select object_id from sys.tables where [name] = 'ADetails' )and [Name] in ('1','2','3','4','5','6','7','8','9','10'))order by [Name] asc


OPEN @cursor

FETCH NEXT

FROM @cursor INTO @colname
WHILE @@FETCH_STATUS = 0
BEGIN



    set @top =  (select CASE WHEN Isnumeric(@colname) = 1
    THEN CONVERT(int,@colname) 
    ELSE 0 END AS COLUMNA)

    if @top  <= '5'
    BEGIN
    set @query =  '(['+cast(@top as nvarchar(10)) + ']) ,'
    set @topass = rtrim(ltrim(@topass)) +' '+rtrim(ltrim(@query))

    END

FETCH NEXT

FROM @cursor INTO @colName
END

CLOSE @cursor
DEALLOCATE @cursor


set @topass =  (SELECT SUBSTRING(@topass,1, len(@topass)-1))

begin


set @TSQL = '
SELECT @R = (MAX(MaxValue) - MIN(MinValue)) FROM ADetails
CROSS APPLY (SELECT MIN(d) MinValue FROM (VALUES '+@topass+' ) AS a(d)) X 
CROSS APPLY (SELECT MAX(d) MaxValue FROM (VALUES '+@topass+' ) AS a(d)) Y
where SumNo= @SumNo'

exec sp_executesql @TSQL, N'@DimItem nvarchar(10), @R decimal(18,3), @SumNo nvarchar(15)', @DimItem, @R output, @SumNo

update ADetails set R = @R where SumNo= @SumNo


end

Upvotes: 0

Views: 670

Answers (1)

Roger Wolf
Roger Wolf

Reputation: 7722

As per docs, you need to specify the output keyword in both the parameter declaration and parameter list when calling sp_executesql:

exec sp_executesql @TSQL, N'@DimItem nvarchar(10), @R decimal(18,3) output, @SumNo nvarchar(15)',
  @DimItem, @R output, @SumNo;

Upvotes: 2

Related Questions