Delete based on either parameters

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)

Delete based on either parameters

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2011-01-23 : 23:33:10

hi

I have a delete stored proc and i want to be able to delete based on either ProdID or ProdName and EmployeeID

Create Proc Delete_tableA
@ProdID as int,
@ProdName as nvarchar(50),
@EmployeeID as nvarchar(50)
as
Delete from TableA
where prodid = @prodid .... how do i continue here? thanks

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2011-01-24 : 02:24:52

hi

I have found the solution

Create Proc Delete_tableA
@ProdID as int = null,
@ProdName as nvarchar(50) = null,
@EmployeeID as nvarchar(50) = null
as
Delete from TableA
where prodid = COALESCE(@prodid,prodid) or (ProdName = COALESCE(@ProdName ,ProdName) and EmployeeID = COALESCE(@EmployeeID ,EmployeeID ))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 11:40:10

hope you realise that this would delete everything from table if you're not passing any parameters to query at all

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

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-24 : 13:55:31

Add some protection against deleteing everything in TableA

CREATE PROCEDURE Delete_TableA
@in_product_id INTEGER = NULL,
@in_prod_name NVARCHAR(50) = NULL,
@in_employee_id NVARCHAR(50) = NULL

DELETE FROM TableA
WHERE product_id = COALESCE(@in_product_id, product_id)
OR (prod_name = COALESCE(@in_prod_name, prod_name)
AND employee_id = COALESCE(@in_employee_id, employee_id ))
AND COALESCE(@in_product_id, @in_prod_name, @in_employee_id) IS NOT NULL;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-24 : 16:00:13

Why the use of COALESCE? Isn't that just simple logic?

Delete from TableA
where 
    prodid = @prodid
or 
(
    ProdName = @ProdName 
    AND EmployeeID = @EmployeeID
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources