Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL return statement not giving expected results

Posted on 2011-03-22
15
Medium Priority
?
438 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 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Loops Section Overview

876 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