Solved

How to write a delete stored procedure

Posted on 2008-10-17
9
361 Views
Last Modified: 2012-05-05
Hi all I have a question. I need to know how to write a delete stored procedure please.
my fields are  @Name nvarchar (50), @Phone nvarchar(50), @Birthday datetime. and they exist in the table called tbl_Bday. any help would be great.
0
Comment
Question by:HawaiiDragon
  • 4
  • 3
  • 2
9 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 22740490
Do you  mean a stored procedure to delete records?

create procedure mydeleteproc
(@name nvarchar(50), @phone nvarchar(50), @birthday datetime)
as
delete from tbl_bday
where name = @name and phone = @phone and birthday = @birthday

go
0
 
LVL 16

Expert Comment

by:Steve Krile
ID: 22741057
chapmandew is correct as usual.

I would add however, that if you are expecting only one row to be deleted, you should consider using a datakey value rather than matching parameters.  You could argue that a Name, plus a Phone number, plus a birthday are unique, but of course we all know to expect the unexpected in DB design.

So, a "safer" delete statement would be something like
DELETE FROM tbl_bday
WHERE tbl_BdayID = @BDayID
0
 

Author Comment

by:HawaiiDragon
ID: 22742059
Yes it needs to be a stored procedure. Im going to try chapmandews solution reall quick and see what I come up with.

any other ideas would be great too.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 16

Expert Comment

by:Steve Krile
ID: 22742120
Well, this is the DELETE portion of a stored procedure



create procedure mydeleteproc
(@BDayID)
as
DELETE FROM tbl_bday
WHERE tbl_BdayID = @BDayID

go
0
 

Author Comment

by:HawaiiDragon
ID: 22742183
okay the procedure was great now im having problems executing it. I have a table and I need the row selected deleted when the user clicks the delete button. It is a datagridview. I think I see my problem but i dont know how to fix it I will put the code below.
    private void btnDelete_Click(object sender, EventArgs e)
        {
            //Inside a try
            try
            {
 
                SqlParameter[] test = new SqlParameter[3];
 
                 // I used the inserts from textboxes and now its in             //data grid view row Any Ideas???
  
              test[0] = new SqlParameter("@Name", tbName.Text);
                test[1] = new SqlParameter("@Phone", tbPhone.Text);
                test[2] = new SqlParameter("@Birthday", dateTimePicker1.Value);
 
                SqlConnection scs = new SqlConnection();
 
                string jaysisawesome = ConfigurationManager.ConnectionStrings["Phone_number_birthday.Properties.Settings.BirthdaysConnectionString"].ToString();
                scs.ConnectionString = jaysisawesome;
 
                SqlCommand cmd = new SqlCommand();
                //cmd.Connection = scs.ConnectionString;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "NewDeleteCommand";
                cmd.Parameters.AddRange(test);
                cmd.Connection = scs;
                cmd.Connection.Open();
                int one = cmd.ExecuteNonQuery();
                cmd.Connection.Close();
 
                if (one > -1)
                {
                    MessageBox.Show("Database Updated");
                    this.Close();
                    PhoneListForm.ActiveForm.Refresh();
                }
                else
                {
                }
            }
            //Close connection in finally
            //Catch problems
            //sql command object
            //command type
            //DataAccess
            //Using AppConfig ConnectionString (pull from)
            //Params
            //ExecuteNonQuery
            catch (Exception ex)
            {
                ex.ToString();
            }
            this.Close();
            PhoneListForm.ActiveForm.Refresh();
        }

Open in new window

0
 
LVL 16

Expert Comment

by:Steve Krile
ID: 22742489
Is there a Key in this database?  If so, you should set up the DataKeyValue for your grid.  Then, you can use the SelectedIndexValue property of the grid to pass the ID of the row you want to delete.

Then, instead of putting all this code on the button click event, you would wire it up to the SelectedIndexValueChanged event of the datagrid.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22742842
To be fair, you should probably submit a new question for your new question.
0
 
LVL 16

Expert Comment

by:Steve Krile
ID: 22743012
True.  In fact, this type of data management is not something I suggest.  The way I prefer is using a class with Get, Set, Update, Delete, and Insert methods.  Then I like using the combination of ObjectDataSources and DataGrids for this type of work.

But, indeed, that is quite a different topic than how to write a Delete proc!  :)
0
 

Author Comment

by:HawaiiDragon
ID: 22743610
true so I have closed this question and will open antoher in just a moment. Please check it out ... im lost... again :)
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
View SQL 2005 Job package 16 43
How to share SSIS Package? 6 37
2016 SQL Licensing 7 41
I have a SQL Select with a where clause that has an issue. 6 40
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question