Solved

SQL return statement not giving expected results

Posted on 2011-03-22
15
377 Views
Last Modified: 2013-12-17
I have a stored procedure which contains 3 return statements, depending on certain conditions:

BEGIN TRAN
    INSERT ......
    
    IF @@ERROR <>0
        BEGIN
            ROLLBACK TRAN
            RETURN 51
        END
    
    SET @variable1 = CAST(SCOPE_IDENTITY() AS INT)
    
    INSERT ......

    IF @@ERROR <>0
        BEGIN
            ROLLBACK TRAN
            RETURN 50
        END

COMMIT TRAN

RETURN 1

Open in new window


So if there's a problem after either of the two inserts, the 51 or 50 is returned and if all goes fine, I'd expect 1 to be returned.  However, if I run the following stored procedure, the outcome integer has 2 (from the two insert statements)

using (Dataset1.QueriesTableAdapter test1 = new Dataset1.QueriesTableAdapter())
{
    ......
    int outcome = 0;
    outcome = test1.uspInsert(field1, field2, ........);
    ......
}

Open in new window


is this correct behaviour or should outcome not contain 1 as that is the last statement to run in the stored procedure?
0
Comment
Question by:sbickerstaff
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 11

Expert Comment

by:Máté Farkas
Comment Utility
Try to start your stored procedure body with a SET NOCOUNT ON statement.
0
 
LVL 23

Expert Comment

by:wdosanjos
Comment Utility
Try adding a SET NOCOUNT ON at the beginning of the stored proc. That will prevent the insert count from being returned.

http://msdn.microsoft.com/en-us/library/ms189837%28v=SQL.100%29.aspx
0
 
LVL 3

Author Comment

by:sbickerstaff
Comment Utility
Ok, I changed my stored proc:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
GO


ALTER ......

Open in new window


(the first two were there automatically when i created the sp) but when I run the code again, it still returns 2.

I've even closed down the VS2010 and opened it again in case there was something cached in the debug session but still get 2.
0
 
LVL 3

Author Comment

by:sbickerstaff
Comment Utility
If I run the stored proc in SSMS, i get return value of 1 in the results pane.  But when I run the above C# code, the integer still has 2
0
 
LVL 4

Expert Comment

by:MeLindaJohnson
Comment Utility
You need to send the "set nocount on" first, then run the stored proc.
0
 
LVL 3

Author Comment

by:sbickerstaff
Comment Utility
MeLindaJohnson: what do you mean before I run the stored proc?

According to the microsoft link supplied by 'wdosanjos', the top of my sp should look like:

USE <dbname>
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Open in new window

0
 
LVL 3

Author Comment

by:sbickerstaff
Comment Utility
if i try to alter the stored proc to add SET NOCOUNT ON it doesn't seem to save it, yet any other changes to the queries, etc within it do successfully change
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 23

Expert Comment

by:wdosanjos
Comment Utility
Your function definition should be something like this:

CREATE FUNCTION YourFunctionName 
(
	-- Your function parameters	
)
RETURNS int
AS
BEGIN
	SET NOCOUNT ON;

        --- Your Code
END

Open in new window

0
 
LVL 4

Expert Comment

by:MeLindaJohnson
Comment Utility
I'm not familiar with C# but I would think you can run everything together as a sql file
filename.sql
in C#, run the sql file.

sqlfile:
USE <dbname>
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
exec stored_proc
0
 
LVL 3

Author Comment

by:sbickerstaff
Comment Utility
@wdosanjos

Ok, I've done this:

USE <dbname>
GO

CREATE PROCEDURE uspInsertTest

/* variabled defined in here */

AS

BEGIN TRAN
    SET NOCOUNT ON;
    
    INSERT ......  
      
    IF @@ERROR <>0  
        BEGIN  
            ROLLBACK TRAN  
            RETURN 51  
        END  
      
    SET @variable1 = CAST(SCOPE_IDENTITY() AS INT)  
      
    INSERT ......  
  
    IF @@ERROR <>0  
        BEGIN  
            ROLLBACK TRAN  
            RETURN 50  
        END  
  
COMMIT TRAN  
  
RETURN 1

Open in new window


Now when I run it, the record is successfully added to the DB but I now get -1
0
 
LVL 4

Expert Comment

by:MeLindaJohnson
Comment Utility
Don't have a clue on the -1.  Maybe if you don't get a zero than all is good? Sorry I'm not much help.
0
 
LVL 23

Expert Comment

by:wdosanjos
Comment Utility
The problem is that you are creating a PROCEDURE not a FUNCTION.  A PROCEDURE by definition does not return a value.  Unfortunately, a FUNCTION cannot contain an INSERT statement.

Try changing the RETURN 1 to SELECT 1.
0
 
LVL 3

Author Comment

by:sbickerstaff
Comment Utility
So does that mean it's no longer accessed by QueriesTableAdapter

i.e.
using (Dataset1.QueriesTableAdapter test1 = new Dataset1.QueriesTableAdapter())
{
    ......
    int outcome = 0;
    outcome = test1.uspInsert(field1, field2, ........);
    ......
}

Open in new window



but is now something like:

using (DataSet1TableAdapters.uspInsertTestTableAdapter test1 = new DataSet1TableAdapters.uspInsertTestTableAdapter())
{
    DataSet1 d1 = new DataSet1();
    Boolean checkFailed = false;
    try
    {
        test1.Fill(d1.uspInsertTest, email);
    }
    catch (Exception error)
    {
        checkFailed = true;
    }
    
    int outcome = 0;
    if ((d1.uspInsertTest.Rows.Count == 1) && (checkFailed == false))
    {
    outcome = 1;
    ......
    ......
    ......
}

Open in new window


When I add the NOCOUNT and change it to SELECT 1, when I update my Dataset in VS2010, it no longer treats it like any other Insert / update / delete.
0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 500 total points
Comment Utility
If think you need to change your approach and use RAISERROR in the proc instead of returning values, so you can better capture the error conditions in the C# code.  Something like this:

USE <dbname>
GO

CREATE PROCEDURE uspInsertTest

/* variabled defined in here */

AS

BEGIN TRAN

    INSERT ......  
      
    IF @@ERROR <>0  
        BEGIN  
            ROLLBACK TRAN  
            RAISERROR('51', 11, 1)
        END  
      
    SET @variable1 = CAST(SCOPE_IDENTITY() AS INT)  
      
    INSERT ......  
  
    IF @@ERROR <>0  
        BEGIN  
            ROLLBACK TRAN  
            RAISERROR('50', 11, 1)
        END  
  
COMMIT TRAN  

Open in new window


Then your code would be:

using (Dataset1.QueriesTableAdapter test1 = new Dataset1.QueriesTableAdapter())
{
    ......
    int outcome = 0;
    try
   {
      int count = test1.uspInsert(field1, field2, ........);
   }
   catch(SqlException ex)
   {
        outcome =  (ex.Number == 50000) ? int.Parse(ex.Message) : -1; /* some other error */
   }
    ......
}

Open in new window

0
 
LVL 3

Author Comment

by:sbickerstaff
Comment Utility
@wdosanjos

that's great, works like a charm.  thank you
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now