stored procedure deletion statement

Santosh Reddy

9y1.2kSQL

Write a script that creates and calls a stored procedure named test. This procedure should include a set of two SQL statements coded as a transaction t o delete the row with an invoice ID of 114 from the invoices table. To do this, you must first delete all line items for that invoice from the invoice_line_items table. If these statements execute successfully, commit the changes, otherwise roll back the changes.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke9y ago
Accepted answer
I always hated to give the solution like this...anyways....here you go!
if exists (select 1 from sys.procedures where name = 'usp_InvoiceDelete')
  drop procedure [dbo].[usp_InvoiceDelete]
go

create procedure [dbo].[usp_InvoiceDelete]
(
  @Invoice_Id [integer]
)
as
begin

  set nocount on;

  -- execute [dbo].[usp_InvoiceDelete] 114;

  begin transaction;

  begin try

    -- delete items first,
    delete
    from [dbo].[invoice_line_items]
    where [invoice_id] = @Invoice_Id

    -- delete invoice.
    delete
    from [dbo].[invoices]
    where [invoice_id] = @Invoice_Id

    commit transaction;
  end try
  begin catch
    rollback transaction;

    -- perform any other exception handling here...
  end catch

end
go
+3
Gnanavel Sekar9y ago
if you have no idea means google it..if stuck anywhere in coding mention the place.
+2
Nitin Sontakke9y ago
So where is the problem? Why do you say "script that creates and calls"?

Have you tried something already?
+2