delete table question in sp - 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 2000 Forums

SQL Server Development (2000)

delete table question in sp

Author

Topic

abarsami
Yak Posting Veteran

68 Posts

Posted - 2004-02-19 : 19:50:09

I have a stored proc where I pass (@id & @type_id)

delete tablea where (id=@id)
and (type_id = case when @type_id=0 then type_id
when @type_id <> 0 then @type_id
END)

But this delete takes very long If @type_id=0, otherwise it's fine. Note: @type_id=0 means that it should ignore type_id where clause and just use id=@id.

If I just use
delete tablea where (id=@id)
it's fine:

Any way around this. I want to use case so I put all into 1 stored procedure.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-19 : 19:55:43

You can do this instead:



CREATE PROC usp_SomeProc
(@id INT, @type_id INT)
AS

IF @type_id = 0
  DELETE FROM TableA
  WHERE id = @id
ELSE
  DELETE FROM TableA
  WHERE id = @id AND type_id = @type_id

RETURN

Tara

abarsami
Yak Posting Veteran

68 Posts

Posted - 2004-02-19 : 19:55:57

Also if selecting:

select * from tablea where (id=@id)
and (type_id = case when @type_id=0 then type_id
when @type_id <> 0 then @type_id
END)

It is very fast. I guess type_id=type_id in the where clause only affects the delete not select statement.

abarsami
Yak Posting Veteran

68 Posts

Posted - 2004-02-19 : 19:58:02

Is there another way to do it without repeating the statement again?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-19 : 19:59:28

Why don't you want to repeat the statement?

Do you have an index on type_id? How selective is that column?

Tara

abarsami
Yak Posting Veteran

68 Posts

Posted - 2004-02-19 : 20:02:07

Because I will have 16 delete statements in the stored procedure. I am just thinking ahead -- If I repeat, then if the stored procedure will change in the future, it will be more work to go in and change.

abarsami
Yak Posting Veteran

68 Posts

Posted - 2004-02-19 : 20:07:05

I think the best thing is to use 2 stored procedures ... one with @type_id = value. The other without using @type_id.

Would be faster than using (if... then) all in 1 stored procedure?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources