1

I am facing an issue in capturing the return code of a stored procedure. I am calling a sybase stored procedure in my korn shell script using isql statement as mentioned below.

isql -U ${APPLID} -S{SERVER}> ${sqlMsg} << EOF
use ${DATABASE}
go
exec stored_procedure
go
EOF

returncode=$?

If an error occur inside the stored procedure, I am not able to capture if I follow the below method.

if [ $returncode -ne 0 ]
then
 print "failed"
fi

I tried using a return statement inside the stored procedure like return (1)

but this didn't give me expected results. When i echoed the returncode, it echoed as 0, even after returning the status as 1. return status as 1 was seen in the sql logs.

Mike Gardner
  • 6,611
  • 5
  • 24
  • 34
user3259912
  • 11
  • 1
  • 2
  • `@@error` stores the error for the last statement. It returns 0 for no error, or a specific error number if an error occured. – Mike Gardner Feb 18 '14 at 14:21

2 Answers2

1

To capture the error inside your sql statement, you will need to look at @@error

returncode=`isql -U ${APPLID} -S{SERVER}> ${sqlMsg} << EOF
             use ${DATABASE}
             go
             exec stored_procedure
             go
             select @@error
             go 
 EOF`

This should allow your if condition to work as you expect.

Mike Gardner
  • 6,611
  • 5
  • 24
  • 34
0

The $! variable returns execution status of the isql program, not the store procedure status. That is the reason why the returncode being echoed prints 0: the isql program itself worked properly.

To catch the output from your store procedure, I would redirect it to an external output file and read it. The isql utility has several options for manipulating with input/output files, such as -i input_file and -o output_file for specifying input and output files accordingly. If your store procedure has an output, it will be sent to the output_file. Now you may read the output_file content from your script by a simple "cat" or more sophisticated loop, depending on the output_file content. To create an output file with a unique name use $$ variable which gives you current PID of the script. Delete the output file at the end. 

#!/bin/bash
output_file=output.$$
isql with all your prameters and -o $output_file
status=`cat $output_file`
echo $status
rm $output_file
MichaelGoren
  • 961
  • 9
  • 15