single stored procedure to delete a record from any table

Question

5.00/5 (1 vote)

See more:

SQL-Server-2005

Hi All,
I want to write a single stored procedure to delete record from any table using any codition i try following but didnt succeeded

SQL

create procedure deleteRecord
(
 @tblName nvarchar(255),
 @condition nvarchar(255)
)
as 
  begin
     delete from @tblName @condition
  end

Help me.
Thanks in Adv.

Posted 22-Nov-11 3:38am

Nilesh Patil Kolhapur

Updated 22-Nov-11 3:43am

Mehdi Gholam

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Not Active · Accepted Answer · 2011-11-22T03:48:00

Solution 2

A one size fits all delete operation would be ill-advised and near impossible to implement with any reliability or security

Posted 22-Nov-11 3:48am

Not Active

Comments

Mehdi Gholam 22-Nov-11 9:52am

Agreed a terrible idea. 5'ed - but if the OP is asking for rope... :)

[no name] 22-Nov-11 10:05am

The one who loads the gun is just as responsible as the one who pulls the trigger

fjdiewornncalwe 22-Nov-11 10:46am

Agreed. A silly idea just asking for abuse.

Mehdi Gholam · Accepted Answer · 2011-11-22T03:45:00

Solution 1

Change the code to the following :

SQL

exec ('delete from ' + @tblName + ' ' + @condition)

Posted 22-Nov-11 3:45am

Mehdi Gholam

Comments

Nilesh Patil Kolhapur 22-Nov-11 10:29am

Thanks.

Amir Mahfoozi 22-Nov-11 11:11am

+5ed to neutralize malicious +1er ! ;)

fjdiewornncalwe 22-Nov-11 11:29am

extremely dangerous. In the example used, if the user puts in exec deleteRecord 'Table1', '' then you will simply delete everything. Where is the validation, how can it be validated, how do you protect your data from abuse.

Mehdi Gholam 22-Nov-11 11:47am

The presumption albeit a far fetched one is the OP knows what he is doing. I just supply the rope :)

Amir Mahfoozi · Accepted Answer · 2011-11-22T04:31:00

Solution 3

Here it is :

SQL

create procedure deleteRecord
(
 @tblName nvarchar(255),
 @condition nvarchar(255)
)
as 
  begin
     declare @cmd as nvarchar(4000)
     
     set @cmd = 'delete ' +  @tblName + ' where ' + @condition
     
     exec sp_executesql @cmd;
  end

You should call it by this way :

SQL

exec deleteRecord 'Table1' , 'id=7'

Hope It Helps.

Posted 22-Nov-11 4:31am

Amir Mahfoozi

Updated 22-Nov-11 4:40am

v2

Comments

Nilesh Patil Kolhapur 22-Nov-11 10:42am

Thanks Amir

Amir Mahfoozi 22-Nov-11 11:08am

You're welcome ;)

fjdiewornncalwe 22-Nov-11 11:29am

extremely dangerous. In the example used, if the user puts in exec deleteRecord 'Table1', '' then you will simply delete everything. Where is the validation, how can it be validated, how do you protect your data from abuse.

Amir Mahfoozi 22-Nov-11 11:34am

I have tested what you claimed and got his error : Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'where'.
Also you should know that I just answered his question, he didn't asked "Is it the correct way or not?" to discuss about it.

fjdiewornncalwe 22-Nov-11 13:18pm

You did, and I'm just adding my two cents worth on the danger of doing it this way so that inexperienced devs who may view this as an easy quick win will think twice about doing it this way.