CODEWITHSUNDEEP
sqlsql-server
Nils Ozols
Nils Ozols

Reputation: 5

Problems with output parameter

I have problem when I run this procedure it gives me back an error trying to parse 'ok' to int but I dont want result to be int.

DECLARE @result_var nvarchar(max)
DECLARE @ParmDefinition NVARCHAR(max)    

EXECUTE Sp_executesql
            @definition,
            @ParmDefinition,
            @result_var = @result_var output;

Code that is changing @result_var

SET @result_var = ''ok''; 

DECLARE @cursor CURSOR, 
        @name VARCHAR(100) 

SET @cursor = CURSOR FOR 
                 SELECT [NAME] 
                 FROM [iflvs08].mds.mdm.lv_budget_employee

OPEN @cursor
FETCH next FROM @cursor INTO @name 

WHILE @@FETCH_STATUS = 0 
BEGIN 
   IF RIGHT(@Name, 1) = '' ''
   begin
      IF (Len(@result_var) > 1) 
         SET @result_var = @result_var + '','' + @name 
      ELSE 
         SET @result_var = @name 
   END

   FETCH next FROM @cursor INTO @name 
END

Upvotes: 0

Views: 43

Answers (1)

olga
olga

Reputation: 111

Looks like that you declared but didn't set up @ParmDefinition variable. It should be like this:

DECLARE @ParmDefinition NVARCHAR(max)
SET @ParmDefinition = N'@result_var nvarchar(max) OUTPUT';    
EXECUTE Sp_executesql
            @definition,
            @ParmDefinition,
            @result_var=@result_var output ;

Upvotes: 1

Related Questions