INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Simple Store Procedure to delete rows in SQL Server 2014

Simple Store Procedure to delete rows in SQL Server 2014

(OP)
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

RE: Simple Store Procedure to delete rows in SQL Server 2014

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:

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.

RE: Simple Store Procedure to delete rows in SQL Server 2014

(OP)
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.

RE: Simple Store Procedure to delete rows in SQL Server 2014

donalexander, A star for Olaf?

Have fun.

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close