STORED PROCEDURE PEFORMING AN INSERT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

STORED PROCEDURE PEFORMING AN INSERT

Author

Topic

stevenandler
Starting Member

42 Posts

Posted - 2012-08-14 : 18:38:47

I would like to know if there is a way to capture a return from a call to a stored procedure to determine if the insert was successful. If the table has an Identity field, then this is straight forward because you can include a check in the stored procedure. see following example:

CREATE PROC dbo.InsertShipper
@CompanyName nvarchar(40) = NULL,
@Phone nvarchar(24) = NULL,
@ShipperID int = NULL OUTPUT
AS
SET NOCOUNT ON;
IF @CompanyName IS NULL
SET @ShipperID = 0;
ELSE
BEGIN
INSERT INTO dbo.Shippers(CompanyName, Phone)
VALUES (@CompanyName, @Phone);
SET @ShipperID = SCOPE_IDENTITY(); END

However, if the table that is being updated does not contain an Identity field but does contain a constraint or unique index, is there a way to capure the results of the insert? If not should I use a function instead of a stored procedure?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-14 : 18:43:15

you can use a variable to capture @@ROWCOUNT and return it to see if rows affected is >0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-08-15 : 01:28:56

Do you mean SUCCESS | FAILURE or jsut an ERROR ? If ERROR than you can use some ERROR management such as RAISERROR

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

stevenandler
Starting Member

42 Posts

Posted - 2012-08-15 : 12:42:22

For Some reason, I still am not receiving back the ResultMessage to my C# program. Here is the code.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace INSERT_NEW_CUSTOMER
{

class Program
{
public static string custid;
public static string companyname;
public void RunStoredProc()
{
SqlConnection conn = null;
SqlDataReader rdr = null;
String result = "";
try
{

conn = new SqlConnection("Data Source=RAY-PC;Initial Catalog=Northwind;User ID=sa;Password=*****");
conn.Open();
SqlCommand cmd = new SqlCommand("dbo.InsertCustomer", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter myParm1 = cmd.Parameters.Add("@CustomerID", SqlDbType.Text, 20);
myParm1.Value = custid;

SqlParameter myParm2 = cmd.Parameters.Add("@CompanyName", SqlDbType.Text, 20);
myParm2.Value = companyname;

rdr = cmd.ExecuteReader();
while (rdr.Read())
{
result = rdr["@ReturnMessage"].ToString();
}
Console.WriteLine("return is " + result);
}

catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}

finally
{
if (conn != null)
{
conn.Close();
}
if (rdr != null)
{
rdr.Close();
}
}
}

The result is always NULL whether the insert was sucessful or failed.

Here is my Stored Procedure.

USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[InsertCustomer] Script Date: 08/15/2012 12:40:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[InsertCustomer]
@CustomerID nvarchar(40) = NULL,
@CompanyName nvarchar(40) = NULL,
@ReturnCode int = NULL OUTPUT,
@ReturnMessage nvarchar(255) = NULL OUTPUT

AS
SET NOCOUNT ON;

IF EXISTS (SELECT CustomerID FROM dbo.Customers
WHERE CustomerID=@customerID)
BEGIN
-- RAISERROR ('Duplicate CategoryName detected.', 16, 1)
SELECT @ReturnCode=0,@ReturnMessage ='Duplicate CustomerID .'
RETURN
END

BEGIN
DECLARE @err int
DECLARE @Rows int;
BEGIN TRANSACTION
INSERT INTO dbo.Customers(CustomerID,CompanyName)
VALUES (@CustomerID,@CompanyName);
-- Pick up @@ERROR and @@ROWCOUNT
SELECT @Err = @@ERROR, @Rows = @@ROWCOUNT

-- If there's an error, rollback.
IF @Err <> 0
BEGIN
SELECT @ReturnCode=@Err,
@ReturnMessage= 'Transaction rolled back.'
ROLLBACK TRANSACTION
RETURN
END
END
-- If no rows inserted, rollback.
IF @Rows = 0
BEGIN
SELECT @ReturnCode=2601,
@ReturnMessage='Insert failed.'
ROLLBACK TRANSACTION
RETURN
END
-- Success! Commit the transaction.
ELSE
BEGIN
COMMIT TRANSACTION
SELECT @ReturnCode=0, --@CategoryID=@identity,
@ReturnMessage='Transaction succeeded, row added.'
RETURN
END

When I execute the Stored procedure from Transac SQL commands, it works fine. Here is the Commands.

DECLARE @ReturnMessage varchar(25);
EXEC dbo.InsertCustomer
@CustomerID='rrrr',
@CompanyName = 'Express2You',
@ReturnMessage = @ReturnMessage OUTPUT;
SELECT @ReturnMessage AS ReturnMessage;
GO

Please tell me what is wrong with my code.

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-15 : 13:01:42

you've not even set output parameters from application. i cant even see ReturnMessage param being added in command definition in c# code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stevenandler
Starting Member

42 Posts

Posted - 2012-08-15 : 14:13:14

Ok I added the ReturnMessage Parameter but what I'm seeing is the execute reader never enters the while loop even once so there is some other problem with my app.

SqlParameter myParm3 = cmd.Parameters.Add("@ReturnMessage", SqlDbType.Text, 20);
// myParm3.Value = result;

rdr = cmd.ExecuteReader();
//app never enters while loop
while (rdr.Read())
{
result = rdr["ReturnMessage"].ToString();
}
Console.WriteLine("return is " + result);
}

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-08-16 : 01:36:02

When you execute the c# code , can you view from SQL Server the actual stored procedure is being executed ?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

stevenandler
Starting Member

42 Posts

Posted - 2012-08-16 : 10:21:45

Yes. If the CompanyId is unique, the record is being added to the table. Also, I am receiving the ReturnMessage when running the Stored Procedure from SSMS so I am almost postitive there is some problem with my code. By the way I have already written other C# programs which can call a stored procedure and return field or even multiple rows back to C#. I'm sure there is some simple thing I must have overlooked

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources