Strange behaviour

  • Hello,

    Using SQL2K sp2, Win2K sp3.

    I am seeing behaviour that I can't explain, perhaps someone here can help me out.

    I have a stored procedure that is working OK. It dumps the results of 3 other procs into a #tmp table, and returns the results. Now, the third of these sub-procs returns null in one of the columns, and the #tmp_table is explicitly defined to not allow nulls in some columns. When I run the code in QA, as expected, an error is generated, and the result set only contains the results of the first 2 sub-procs. So far, so good.

    However, when I call the sproc from the application (PowerBuilder, using the native SQL driver), I get the complete result set (i.e., the results I would expect from all three sub-procs executing successfully). No error. I added a couple of inserts to prove to myself that the code is actually passing through the third sproc, which it indeed is.

    So I am completely mystified.

    Any ideas?

    Thanks,

    Paul

  • Maybe raise an error explicitly in stored procedure 3 an check if the application handle the error correctly[?}

  • It might be that the proc when it runs is ignoring the NULL values and is returning all of the NON-NULL data.

    Are you sure you want your application to fail on NULL? If yes why don't you query your returned result set and RAISERROR if you find a NULL?

    AJ Ahrens

    webmaster@kritter.net



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • 5409045121009 and whoteegan:

    Thank you for the replies.

    Raiserror in sproc #3 is handled correctly. No results are returned to the application, and the error message is displayed where expected.

    whoteegan: Application not failing on NULL, sproc is. Somehow, when application calls sproc, results that should be NULL are returned, with the valid data.

    And, Yes, I want the application to fail on errors, all business logic is in the back-end.

    Thanks,

    P

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply