stored procedure deletion statement

Question

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.

Nitin Sontakke · 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

Gnanavel Sekar · Answer

if you have no idea means google it..if stuck anywhere in coding mention the place.

Nitin Sontakke · Answer

So where is the problem? Why do you say script that creates and calls? Have you tried something already?