Simple Store Procedure to delete rows in SQL Server 2014

[donalexander]

Hello,

I want to create a store procedure, which I have never done before and intimidating. I want to delete all the rows in a table my coding will be below


USE [ATAS00_app]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Delete rows in General Ledger table

DELETE dbo.GLDetl


Do I need to add/code anything else will the table be close automatically? Do I have to worry about anything maintenance on the table? Thank you in advance

 

[Olaf Doschke]

First of all you don't open and close tables.

To create a stored procedure you'll need a CREATE PROCEDURE Script, but for a simple DELETE you don't need a stored procedure, execute the DELETE as direct adhoc command is fast enough. The opinions differ, some do all things in stored proc, so it's your choice.

You start the SSMS, connect to your SQL server, expand Databases->ATAS00_app->Programmability. You right click on the Stored Procedures node and select the item "New Stored Procedure"

This'll give you this skeleton of code:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
	-- Add the parameters for the stored procedure here
	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

Don't copy over this code, this is just to show you in advance what to expect and to see you arrive at the right place.

Here you can fill in the procedure name directly after CREATE PROCEDURE (overwrite the part in the pointy brackets). You don't need parameters, so delete the next three lines.
Within BEGIN and END in your case you only need the DELETE, the code there is just a suggestion.

After you're done don't forget to execute this (via the red !Execute)

After that you'll be able to EXEC whatevername and that'll delete the table contents, all rows.

You might want to TRUNCATE TABLE <yourtable> instead, as TRUNCATE TABLE removes all data without causing logging of the row deletions into he transaction log. Delete can cause any of the following: a) DELETE Triggers, b) constraints and c) cascades defined for referential integrity of the data. b) means you may not delete a ledger row, unless all detail data referencing it is deleted beforehand (a constraint can stop the deletion) c) is the other way around and means deleting a ledger row causes the deletion of child data referencing it. TRUNCATE does not cause neither of a)-c).

Bye, Olaf.

 

[donalexander]

Olaf,


Thanks for guding me on now I have a better idea. You are right I think I better do Truncate then delete. I really appreciate the help. Thanks again.

 

[Andrzejek]

donalexander, A star for Olaf?

Have fun.

---- Andy

There is a great need for a sarcasm font.