Solved

SQL return statement not giving expected results

Posted on 2011-03-22
15
399 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 12

Expert Comment

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

Expert Comment

by:wdosanjos
ID: 35191475
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
ID: 35191542
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 3

Author Comment

by:sbickerstaff
ID: 35191721
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
ID: 35191841
You need to send the "set nocount on" first, then run the stored proc.
0
 
LVL 3

Author Comment

by:sbickerstaff
ID: 35191922
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
ID: 35191942
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
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35191990
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
ID: 35192013
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
ID: 35192095
@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
ID: 35192127
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
ID: 35192253
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
ID: 35192438
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
ID: 35202801
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
ID: 35205245
@wdosanjos

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

831 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