Delete record only if no subrecords exist

I have an aspx page that runs a stored procedure against a sql server 2005 database. The stored procedure checks to see if lines exist for a header, and if no lines exist, allows the header to be deleted. Something was askew in my original sproc and the user was able to delete a header that did have lines associated with it. I have re-written the sproc and would like to verify that the way it is written now, that one of three things happen.

1) If header exists with no lines, delete the header and set message = 1 (successfully deleted) and exit

2) If header exists and has lines, do NOT delete the header and set message = 2 (lines exist) and exit

3) If no header exists at all, set message = 3 (no header exists) and exit

Then the aspx page displays the return message back to the user. Here is my sproc.
USE [fin_pilot]
GO
/****** Object:  StoredProcedure [dbo].[AER_DELETE_INVOICE_HEADER]    Script Date: 09/12/2013 11:18:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ====================================================================================================================
-- Author:		Carla M Romere
-- Create date: 08-27-2013
-- Description:	Procedure to delete an invoice header ONLY if there are no lines associated with that invoice header
-- ====================================================================================================================
ALTER PROCEDURE [dbo].[AER_DELETE_INVOICE_HEADER] 
@INVNUM AS INT,
@USERID AS VARCHAR(10),
@MESSAGE AS INT OUTPUT
AS

BEGIN
SET NOCOUNT ON;
DECLARE @LINES AS INT;

--Check that invoice exists
IF (SELECT COUNT(*) FROM SALES_ORDER_INVOICES WHERE INVOICE_NUMBER=@INVNUM) = 0
SET @MESSAGE = 3;

SET @LINES = (SELECT COUNT(*) FROM SALES_ORDER_INVOICE_LINES WHERE INVOICE_NUMBER=@INVNUM);
IF (@LINES = 0 AND @MESSAGE IS NULL)

	BEGIN
	--Delete invoice and set message to success
		DELETE FROM dbo.SALES_ORDER_INVOICES 
		WHERE INVOICE_NUMBER = @INVNUM;
		SET @MESSAGE = 1;

		BEGIN
			INSERT INTO [fin_prod].[dbo].[AER_AUDIT_DELETED_INVOICES]
			   ( [DeleteDateTime],[UserId],[InvoiceDeleted] )
			VALUES ( GETDATE(), @USERID, @INVNUM )
		END
	END

--If lines exist and invoice not deleted, set to lines exist
ELSE 
SET @MESSAGE = 2;

END

Open in new window

Carla RomereDirector of Information TechnologyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Carla RomereDirector of Information TechnologyAuthor Commented:
In case it helps to make things clear, here is my aspx code behind file.
        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            string sqlmessage = "";
            SqlConnection conn = new SqlConnection("Server=sdwinsql03v; database=fin_prod; User Id=intranet; Password=xxx!");
            SqlCommand comm = new SqlCommand("AER_DELETE_INVOICE_HEADER", conn);

            string userid = Request.ServerVariables["AUTH_USER"];
            int pos = userid.IndexOf('\\');
            if (pos > 0)
            {
                userid = userid.Substring(pos + 1);
            }
            SqlParameter message = new SqlParameter("@MESSAGE", SqlDbType.Int,1);
            message.Direction = ParameterDirection.Output; 
            comm.Parameters.AddWithValue("@INVNUM", txtInvNum.Text);
            comm.Parameters.AddWithValue("@USERID", userid);
            comm.Parameters.Add(message);
            comm.CommandType = CommandType.StoredProcedure;

            conn.Open();
            comm.ExecuteNonQuery();

            if ((int)message.Value == 1)
            {
                sqlmessage = "Invoice Header " + txtInvNum.Text + " has been deleted successfully.";
            }

            if ((int)message.Value == 2)
            {
                sqlmessage = "Invoice Header " + txtInvNum.Text + " has not been deleted because invoice lines still exist.";
            }

            if ((int)message.Value == 3)
            {
                sqlmessage = "Invoice Header " + txtInvNum.Text + " does not exist.";
            }

            conn.Close();
            lblMessage.Text = sqlmessage.ToString();
            txtInvNum.Text = "";
            txtInvNum.Focus();
        }

Open in new window

0
Scott PletcherSenior DBACommented:
I think you've got one logic error, and you can tighten up the code:


USE [fin_pilot]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ====================================================================================================================
-- Author:            Carla M Romere
-- Create date: 08-27-2013
-- Description:      Procedure to delete an invoice header ONLY if there are no lines associated with that invoice header
-- ====================================================================================================================
ALTER PROCEDURE [dbo].[AER_DELETE_INVOICE_HEADER]
@INVNUM AS INT,
@USERID AS VARCHAR(10),
@MESSAGE AS INT OUTPUT
AS
SET NOCOUNT ON;

--Check if invoice exists
IF NOT EXISTS(SELECT 1 FROM SALES_ORDER_INVOICES WHERE INVOICE_NUMBER=@INVNUM)
    SET @MESSAGE = 3
ELSE
-- Invoice exists: does it have at least one line?
IF EXISTS(SELECT 1 FROM SALES_ORDER_INVOICE_LINES WHERE INVOICE_NUMBER=@INVNUM)
    SET @MESSAGE = 2
ELSE
BEGIN
-- Invoice exists but doesn't have any lines -- delete it
    DELETE FROM dbo.SALES_ORDER_INVOICES
      WHERE INVOICE_NUMBER = @INVNUM;
      SET @MESSAGE = 1;
      INSERT INTO [fin_prod].[dbo].[AER_AUDIT_DELETED_INVOICES]
         ( [DeleteDateTime],[UserId],[InvoiceDeleted] )
      VALUES ( GETDATE(), @USERID, @INVNUM )
END --ELSE
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Carla RomereDirector of Information TechnologyAuthor Commented:
I've looked at the code it is preventing me from deleting an invoice that has lines. However, I can't figure why when I put in an invoice header that does not exist, instead of getting errorr 3, I'm getting error 2, which is not deleted because lines still exist.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Scott PletcherSenior DBACommented:
That's the "one logic error" to which I was referring, that my code above corrects.
0
Carla RomereDirector of Information TechnologyAuthor Commented:
That's just it, I'm using your code.
0
Scott PletcherSenior DBACommented:
That doesn't seem possible.  My code checks for invoice existence first, and the other code is not even executed unless the invoice exists, so there's no way for @MESSAGE to be changed to 2, as in your original.

I'd need to see the code.  You must have made some type of change to it.
0
Carla RomereDirector of Information TechnologyAuthor Commented:
Here is the stored procedure:
ALTER PROCEDURE [dbo].[AER_DELETE_INVOICE_HEADER] 
@INVNUM AS INT,
@USERID AS VARCHAR(10),
@MESSAGE AS INT OUTPUT
AS
SET NOCOUNT ON;

--Check if invoice exists
IF NOT EXISTS(SELECT 1 FROM SALES_ORDER_INVOICES WHERE INVOICE_NUMBER=@INVNUM)
    SET @MESSAGE = 3
ELSE

-- Invoice exists: does it have at least one line?
IF EXISTS(SELECT 1 FROM SALES_ORDER_INVOICE_LINES WHERE INVOICE_NUMBER=@INVNUM)
    SET @MESSAGE = 2
ELSE

BEGIN
-- Invoice exists but doesn't have any lines -- delete it
    DELETE FROM dbo.SALES_ORDER_INVOICES 
      WHERE INVOICE_NUMBER = @INVNUM;
      SET @MESSAGE = 1;
      INSERT INTO [fin_prod].[dbo].[AER_AUDIT_DELETED_INVOICES]
         ( [DeleteDateTime],[UserId],[InvoiceDeleted] )
      VALUES ( GETDATE(), @USERID, @INVNUM )
END

Open in new window


Here is my aspx code behind (which I have modified some):
        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            lblMessage.Text = "";
            string sqlmessage = "";
            SqlConnection conn = new SqlConnection("Server=sdwinsql03v; database=fin_pilot; User Id=intranet; Password=xxx");
            SqlCommand comm = new SqlCommand("AER_DELETE_INVOICE_HEADER", conn);

            string userid = Request.ServerVariables["AUTH_USER"];
            int pos = userid.IndexOf('\\');
            if (pos > 0)
            {
                userid = userid.Substring(pos + 1);
            }
            SqlParameter message = new SqlParameter("@MESSAGE", SqlDbType.Int,1);
            message.Direction = ParameterDirection.Output; 
            comm.Parameters.AddWithValue("@INVNUM", txtInvNum.Text);
            comm.Parameters.AddWithValue("@USERID", userid);
            comm.Parameters.Add(message);
            comm.CommandType = CommandType.StoredProcedure;

            conn.Open();
            comm.ExecuteNonQuery();

            if ((int)message.Value == 1)
            {
                sqlmessage = "Invoice Header " + txtInvNum.Text + " has been deleted successfully. Click here to delete another invoice.";
            }

            if ((int)message.Value == 2)
            {
                sqlmessage = "Invoice Header " + txtInvNum.Text + " has not been deleted because invoice lines still exist. Click here to delete another invoice.";
            }

            if ((int)message.Value == 3)
            {
                sqlmessage = "Invoice Header " + txtInvNum.Text + " does not exist. Click here to delete another invoice.";
            }

            conn.Close();
            btnError.Text = sqlmessage.ToString();
            btnError.Visible = true;
            btnSubmit.Visible = false;
            txtInvNum.Visible = false;
         }

Open in new window

The problem would have to be on my aspx page then right? I've put in a bogus invoice header on a fresh page as well as on the page after it's posted back another invoice and I get error message 2 every time. If I put in a valid invoice header that does not have lines, I get the correct message. If I put in a valid invoice header that does have lines, I get the correct message. The only one that isn't correct is if the header doesn't exist to begin with.
0
QuinnDexCommented:
something must exist for this line

IF NOT EXISTS(SELECT 1 FROM SALES_ORDER_INVOICES WHERE INVOICE_NUMBER=@INVNUM)
    SET @MESSAGE = 3

Open in new window


try running this direct in SSM and see what the result is. Do you get nothing returned or do you get a row of nulls, hard code @INVNUM

SELECT 1 FROM SALES_ORDER_INVOICES WHERE INVOICE_NUMBER=@INVNUM

Open in new window

0
Carla RomereDirector of Information TechnologyAuthor Commented:
It seems to be working now. Very bizarre.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.