Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL return statement not giving expected results

Posted on 2011-03-22
15
Medium Priority
?
434 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 13

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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 2000 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

Cloud Training Guides

FREE GUIDES: In-depth and hand-crafted Linux, AWS, OpenStack, DevOps, Azure, and Cloud training guides created by Linux Academy instructors and the community.

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
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.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

671 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