Set return value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Set return value

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-02-12 : 17:00:20

How can I code to create a return value if there were no errors. Below code captures only error. Can I use an if ... else....end (like 'if @error = -1 then .... else ... end?)

Exec ('Call QGPL.sp_alcobj(?,?,?,?)', @Library, @File, @Member, @Error) AT AS400SRV_IBMDASQL
if @Error = -1 
	SET @RetValue = 'Error: occurred allocating file ' + @File +  ' and member' + @Member
	RETURN

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 17:05:10

With

IF @@Error =0

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-12 : 17:10:26

Not quite sure what you're asking. Are you talking about within MS SqlServer Stored procedure? Is your @RetValue an OUTPUT Parameter (any datatype), RETURN Code (integer), or perhaps a Code or message from a RAISERROR call?

One thing with your code above: if you have more than one conditional statement you need to use BEGIN/END blocks.

Be One with the Optimizer
TG

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-02-13 : 08:05:49

I will try to explain better what I am looking for.

From Sequel Server I am calling multiple stored procedures on db2/400 (below is snippet calling 2 procs on db2/400)

Sequel Server Script:

@RetValue char(200),
@Library  nvarchar(10),
@File nvarchar(10),
@Member nvarchar(10),
@Error int

Exec ('Call QGPL.sp_ovrdbf(?,?,?,?)', @Library, @File, @Member, @Error) AT AS400SRV_IBMDASQL
if @Error = -1 
	SET @RetValue = 'Error: occurred overding to file member ' + @Member
	RETURN

Exec ('Call QGPL.sp_alcobj(?,?,?,?)', @Library, @File, @Member, @Error) AT AS400SRV_IBMDASQL
if @Error = -1 
	SET @RetValue = 'Error: occurred allocating file ' + @File +  ' and member' + @Member
	RETURN

Code on AS/400:

create procedure sp_alcobj                                               
     (in @library char(10),                                              
      in @file char(10),                                                 
      in @member char(10),                                               
      inout @error int)                                                  
                                                                         
      language sql                                                       
                                                                         
begin                                                                    
                                                                         
declare command char(84);                                                
declare commandLength decimal(15,5);                                     
                                                                         
        -- Exit handlers                                                 
         Declare EXIT HANDLER For SQLEXCEPTION                           
            Set @error = -1;  -- Exception Error                         
                                                                         
set command = 'alcobj obj((' || trim(@library) || '/' || @file ||        
              ' *file *excl ' || trim(@member) || '))';                  
                                                                      set commandLength = decimal(length(trim(command)),15,5);     
 call qsys/qcmdexc(command,commandLength);                    
                                                              
 end;

For each sp being called on db2/400 I need to capture any error conditions (which I think I'm doing). If there is no error I would like to return a successful message to my vb program as well. That is why I was asking if I could use an if @error = -1 'error message' else 'succesful'. Hope this explains better what I am after.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-13 : 08:20:17

RETURN @Error

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 09:00:29

In SQL Server, you can only return integer value from stored procedure directly. otherwise you need to use output parameters.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-13 : 09:45:14