selective delete - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

selective delete

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-02-10 : 12:49:30

Greetings

I want to be able to do the following in one sproc if at all possible.
user can said 1, 2 or three parms

delete from x
where id = @id

delete from x
where id = @id
and dd = @dd

delete from x
where id = @id
and dd = @dd
and tt = @tt

how can I do my deletes using the third syntax even when parm 1 and parm 2 are not sent to sproc?

Thanks

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-02-10 : 13:46:43

Not sure I understand how you want to handle when the parameters are not passed (NULL?). But, I think there are several options:
1. You could use dynamic SQL to build the proper statement based on the parameter.
2. Try some thing like WHERE (ID = @ID OR @ID IS NULL)...

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-02-10 : 14:05:38

thanks senor Lamprey, I think I will go with option #2.


delete from x
 where (id = @id OR @id IS NULL)
   and (dd = @dd  OR @dd  IS NULL)
   and (tt= @dd  OR @tt  IS NULL)

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 15:59:13

quote:
Originally posted by yosiasz

thanks senor Lamprey, I think I will go with option #2.

delete from x
where (id = @id OR @id IS NULL)
and (dd = @dd OR @dd IS NULL)
and (tt= @dd OR @tt IS NULL)

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

keep in mind that this results in not created efficient query plans and would hurt performance especially if dataset is large
so in such cases, dynamic sql would be better method

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-02-10 : 16:10:34

this is for a key value store service and it needs to be blazing fast. So I can see this being a bottleneck since we are with a large data set but I despise dynamic sql but maybe I do not have any choice.

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 16:18:53

quote:
Originally posted by yosiasz

this is for a key value store service and it needs to be blazing fast. So I can see this being a bottleneck since we are with a large data set but I despise dynamic sql but maybe I do not have any choice.

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

just see how it performs with OR solution and then decide whether its sufficient for you.
also consider the level of data growth

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-02-10 : 16:50:57

Also consider what will happen if all the paramters are null.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-02-13 : 12:08:17

yikes! that could truncate the whole table if not careful.

i think I am just going to go explicit on this and have multiple if statements.

Thank you all

quote:
Originally posted by Lamprey

Also consider what will happen if all the paramters are null.

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-13 : 12:34:44

[code]

IF @id IS NOT NULL AND @dd IS NOT NULL AND @tt IS NOT NULL
DELETE
FROM x
WHERE id = @id
AND dd = @dd
AND tt = @tt

IF @id IS NOT NULL AND @dd IS NOT NULL
DELETE
FROM x
WHERE id = @id
AND dd = @dd

IF @id IS NOT NULL
DELETE
FROM x
WHERE id = @id

IF @id IS NULL
GOTO Sproc_Error

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-02-13 : 13:34:17

yes sir!

quote:
Originally posted by X002548

IF @id IS NOT NULL AND @dd IS NOT NULL AND @tt IS NOT NULL
DELETE
FROM x
WHERE id = @id
AND dd = @dd
AND tt = @tt

IF @id IS NOT NULL AND @dd IS NOT NULL
DELETE
FROM x
WHERE id = @id
AND dd = @dd

IF @id IS NOT NULL
DELETE
FROM x
WHERE id = @id

IF @id IS NULL
GOTO Sproc_Error

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-02-13 : 13:55:46

I'd say that you'd need to create more combinations (ie what if only @dd is not null?). This type of query lends itself to Dynamic SQL. But, if there are only 3 parameters you might just do some basic validation. For example:

IF COALESCE(@id, @dd, @tt) IS NULL
BEGIN
    RAISERROR('At lease one parameter must not be null.', 11, 0) -- or GOTO error or how ever you handle errors..
END
ELSE
BEGIN
    delete from x
    where (id = @id OR @id IS NULL)
    and (dd = @dd  OR @dd  IS NULL)
    and (tt= @dd  OR @tt  IS NULL)
END

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-13 : 14:08:05

Depends on the rules...

So, Dude, what are the rules

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2012-02-13 : 14:22:33

How many rows will be deleted? Lots? If so you might need to optimise around that.

If you had a NOT NULL boolean column (e.g. called [IsDeleted]) you could UPDATE that to TRUE instead of using a DELETE (no index changes, no row becoming wider and needing moving to fresh space), and then have an overnight housekeeping job that physically deleted them.

Of course all your queries would have to include "AND IsDeleted = 0" and you'd need to include "IsDeleted = 0" as a filter in all your indexes ... so not a 5-minute-job to implement. But it should satisfy your requirements for "it needs to be blazing fast"

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-02-13 : 14:28:30

Brett

Dude good question. let me gather the rules.

Thanks

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources