Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    168

    Unanswered: Return Resultset from SQL SERVER as output parameter to VB.NET application

    I need to return a resultset consisting of database errors from SQL Server stored procedure's CATCH CLAUSE but stuck with it. Do I need to use cursors to return resultset and if so, then what is the type declaration for the OUTPUT parameter in my .NET application? I tried 'Object' and 'Variant' but did not work.

    I also tried the simple way just using a SELECT statement to return and it works with one stored procedure but not with another as thus in my CATCH CLAUSE:

    Code:
    while (@I <= @count)
     begin      
        BEGIN TRY       
    
            -- delete all previous rows inserted in @customerRow for previous counts @I     
            delete from @customerRow
            -- this is inserting the current row that we want to save in database
            insert into @customerRow 
            SELECT  [id],[firstname], [lastname], [street], [city],
                    [phone],[mobile],[fax], [email], [companyName],
                    [licence],[brn], [vat], [companyStreet], [companyCity], [status]
            FROM    (
                        SELECT  ROW_NUMBER() OVER (ORDER BY id ASC) AS rownumber,
                                [id],[firstname], [lastname], [street], [city],
                                [phone],[mobile],[fax], [email], [companyName],
                                [licence],[brn], [vat], [companyStreet], [companyCity], [status]
                        FROM    @registerDetails
                    ) AS foo
            WHERE rownumber = @I                                                
            -- this stored procedure handles the saving of the current customer row just defined above
            -- if there is any error from that sproc, it will jump to CATCH block 
            --save the error message in the temp table and continue 
            --with the next customer row in the while loop.
            exec dbo.sp_SaveCustomer @customerRow
        END TRY
        BEGIN CATCH
            IF @@TranCount = 0
            -- Transaction started in procedure.
            -- Roll back complete transaction.
            ROLLBACK TRANSACTION;
            if XACT_STATE()= -1 rollback transaction
    
            DECLARE @ErrorMessage NVARCHAR(4000);
            DECLARE @ErrorSeverity INT;
            DECLARE @ErrorState INT;
    
            SELECT @ErrorMessage = ERROR_MESSAGE() + ' ' + (select firstname from @registerDetails where id=@I)
            SELECT @ErrorSeverity = ERROR_SEVERITY();
            SELECT @ErrorState = ERROR_STATE() 
    
    
            INSERT INTO #registrationResults (error,id)
            SELECT @ErrorMessage, @I 
    
        END CATCH       
        set @I = @I +1              
    end 
    COMMIT TRANSACTION registerTran
    
    select * from #registrationResults
    This does not work when select is made in the CATCH block, ie it returns no rows to my .NET application:

    Code:
      begin catch
        IF @@TranCount > 0 or XACT_STATE()= -1 ROLLBACK TRANSACTION;
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;
        DECLARE @ErrorLine INT;
    
    
        SELECT @ErrorMessage = ERROR_MESSAGE();
        SELECT @ErrorSeverity = ERROR_SEVERITY();
        SELECT @ErrorState = ERROR_STATE();
        SELECT @ErrorLine = ERROR_Line();
    
        INSERT INTO #registrationResults (error,id)
        SELECT @ErrorMessage, NULL
    
        select * from #registrationResults
    
    end catch
    Just to summarize the code and where the problem is. The code that works uses a SELECT * from temporary table not in the CATCH block and this works while the one that uses the same SELECT * from temporary table inside the CATCH does not return anything! Strange. The Wrong one is used in another Stored procedure from the Right one btw so am wondering why the same code does not work in those two different situations. Any suggestions?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I am not near a server to test with at the moment, but I am willing to bet that the placement of the commit transaction in the first example is what is complicating things for you. In case of an error, that commit tran statement will throw an error (No transaction is currently open), and the code likely exits. Right before the select * from the temp table.

  3. #3
    Join Date
    Jul 2009
    Posts
    168
    Good try MCrowley but you lost the bet mate Actually the first example works fine but a good try someone pointed out the same thing with the problematic 2nd example with the Commit. Tried to comment that out just for a test but still not returning any resultset to .NET application.

  4. #4
    Join Date
    Jul 2009
    Posts
    168
    Finally found out the crap that was causing the error! CANT BELIEVE IT! Modified the SECOND example where it was not working to show the error that I did not include in the post thinking it was not necessary !!!! Complete nightmare...


    Code:
     begin catch
        IF @@TranCount > 0 or XACT_STATE()= -1 ROLLBACK TRANSACTION;
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;
        DECLARE @ErrorLine INT;
    
    
        SELECT @ErrorMessage = ERROR_MESSAGE();
        SELECT @ErrorSeverity = ERROR_SEVERITY();
        SELECT @ErrorState = ERROR_STATE();
        SELECT @ErrorLine = ERROR_Line();
    
            ****ERROR -- THIS SELECT WAS MESSING ALL UP as it was this select that was being        
        returned to the .NET and not the select of the desired #temp table after, hence returning 0 resultset as this select was EMPTY. !!  
        select status_indicator from InsertInvoiceTriggerData where session_GUID =   guid**
        delete from InsertInvoiceTriggerData where session_GUID = @guid**
    
        INSERT INTO #registrationResults (error,id)
        SELECT @ErrorMessage, NULL
    
        select * from #registrationResults
    
    end catch

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •