| Home | Previous Lesson: Retrieving Multiple Result Sets Next Lesson: Dynamic SQL |
In SQL Server, you can get the changed value of a stored procedure parameter, by declaring the parameter as OUTPUT. For example:
Declare TestProc1 @Var1 Int=NULL OUTPUT
AS
select * from trans
select @Var1 = @@RowCount
return
In the above example, we are assigning the number (count) of rows returned by the first SELECT statement to the @Var1 variable. @Var1 is a parameter to the stored procedure. When the client executes this stored procedure, it gets the value of @Var1. Please note the way of assigning defaults to the stored procedure parameter when user does not pass any value. In the above example, we are assigning NULL value as default to the parameter @Var1. If you want to display error message when the user doesn't pass parameter value, then do not assign a default, for ex: Declare TestProc1 @Var1 Int OUTPUT'. In this case, Sybase automatically generates an error when parameter value is not specified at execution time.
In PowerBuilder, to retrieve the OUTPUT variable value, you need to do one more FETCH after you see 100 in the SQLCA.SQLCode, similar to the one you did in the "Multiple Result Set" stored procedure section. Make sure that the number of variables you put in the INTO clause are equal to the number of variables that are declared with OUTPUT keyword.
| Home | Previous Lesson: Retrieving Multiple Result Sets Next Lesson: Dynamic SQL |