Solved

SQL return statement not giving expected results

Posted on 2011-03-22
15
406 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
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.

 
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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Syntax: How to force case sensitive query? 2 44
parsing JSON help 1 20
Open a link in vb.net 2 15
(sql serv16)ssis 2016 question/check 1 61
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
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…

791 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