Article ID: 151599
Article Last Modified on 3/14/2005
APPLIES TO
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 2000 64-bit Edition
This article was previously published under Q151599
SUMMARY
When you process results from an Embedded SQL (ESQL) application, you must always take into account that ESQL only processes a single result set. This is most likely to become an issue when you execute a stored procedure by using the EXECUTE IMMEDIATE statement or when you use a CURSOR.
back to the top
Example
Here is an example stored procedure that is named spTest:
Create procedure spTest as select au_lname from authors select au_fname from authors go
In a DB-Library application, the spTest stored procedure is treated and retrieved as two result sets:
- au_lname -and-
- au_fname
The DB-Library application calls the dbresults function multiple times in conjunction with the dbcmdrow function to determine the result set information. By design, ESQL is platform independent, allowing you to use the same source code and another precompiler to access a different DBMS environment. To do this, ESQL handles each statement as a single result set.
If you execute the spTest stored procedure, it really produces two result sets from SQL Server; however, the ESQL application will only see the first result set.
The following code sample contains examples of how to execute a stored procedure:
EXEC SQL BEGIN DECLARE SECTION;
char strTest[15] = "spTest";
char strName[51] = "";
EXEC SQL END DECLARE SECTION;
//
// Install the error handlers
//
EXEC SQL WHENEVER SQLERROR CALL ErrorHandler();
EXEC SQL WHENEVER SQLWARNING CALL ErrorHandler();
EXEC SQL WHENEVER NOT FOUND CALL ErrorHandler();
.
.
.
//
// Using a cursor
//
EXEC SQL DECLARE C_2 CURSOR FOR STMT1;
EXEC SQL PREPARE STMT1 FROM :strTest;
EXEC SQL OPEN C_2;
<BR/><BR/>
while(SQLCODE == 0)
{
EXEC SQL FETCH C_2 INTO :strName;
.
.
.
//
// Using EXECUTE IMMEDIATE
//
EXEC SQL EXECUTE IMMEDIATE :strTest;
.
.
.
SQLCODE Settings
Depending on how you try to process the results, you may get different SQLCODE settings. When you are processing the results as a cursor, the OPEN statement returns (0), and the FETCH statement continues to return (0) until it reaches the end of the first result set. Then, it returns (100) as expected.
The EXECUTE IMMEDIATE statement returns a SQLCODE of (1). As documented, the EXECUTE IMMEDIATE statement cannot return result rows.
If you add a RAISERROR statement to the stored procedure, it may make it even more difficult. For example:
Create procedure spTest as RAISERROR(50001, 1, 1) select au_lname from authors go
The RAISERROR statement will be treated as the result set that does not return any result rows. In the case of the OPEN statement, the SQLCODE is set to -50001. In the EXECUTE IMMEDIATE statement, the SQLCODE is set to -50001. The RAISERROR statement is considered to be the result set, and you cannot access the result rows returned from the SELECT statement.
Reversing the stored procedure changes the behavior. For example:
Create procedure spTest as select au_lname from authors RAISERROR(50001, 1, 1) go
Again, you get the SELECT statement result set, but you do not see the RAISERROR of -50001 from the OPEN statement. However, the EXECUTE IMMEDIATE statement sets the SQLCODE to 1 because result rows were returned.
In all these examples, you can change the behavior by not returning results. This means that if you take the third variation of the stored procedure and you add a where clause, such as
where au_lname = '12'
the computer no longer returns result rows; it returns an empty result set.
In this case, the SQLCODE is set to -50001 for both the OPEN and the EXECUTE IMMEDIATE statements, because the first result set did not return rows.
Finally, if you try to ignore the SQLCODE, you get unpredictable results. If you run a RAISERROR statement, and then run a SELECT statement, you can modify the cursor code. For example:
While((SQLCODE == 0) || (SQLCODE == -50001))
{
EXEC SQL FETCH C_2 INTO :strName;
.
.
.
This specific case permits you to process the results from the SELECT statement.
Microsoft does not recommend or support this method of ESQL result set processing. You must always guarantee that only one result set can be returned from a statement.
back to the top
Additional query words: 4.22 esqlc 6.00
Keywords: kbhowtomaster KB151599
