While operator - 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)

While operator

Author

Topic

makimark
Starting Member

34 Posts

Posted - 2002-05-23 : 04:58:21

Hi
I have a stored procedure to delete client records in various tables linked through a (clid) GUID. I have the clid's in one table and want the stored proc to use those clid's one by one in order to delete the records. I tried something like this

Declare @clid uniqueidentifier
While (Select count(clid) from Table1) < 300
Begin
Execute sp_deleteclient @clid
End

Problem is count does not work with GUID and i don't want to pass every clid on its own.

I'm sure there's a guru who has just the answer ?

Mark

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-05-23 : 05:15:25

First of all, don't preface your stored procedures with sp_ - this causes SQL to look in the master database for them first.

Secondly, why do you want to do this one by one? Can you do it as a batch using a subquery?

makimark
Starting Member

34 Posts

Posted - 2002-05-23 : 05:37:51

quote:

First of all, don't preface your stored procedures with sp_ - this causes SQL to look in the master database for them first.

Secondly, why do you want to do this one by one? Can you do it as a batch using a subquery?

Hi

If i can somehow pass the clids(guids) in the one table to the sp that would work;

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-05-23 : 05:52:47

How about:

DELETE FROM tbl_name
WHERE tbl_name.clid IN
(SELECT clid from lu_table_name
where ....)

Not sure exactly what you're trying to do with the count statement.

HTH

makimark
Starting Member

34 Posts

Posted - 2002-05-23 : 05:57:31

quote:

How about:

DELETE FROM tbl_name
WHERE tbl_name.clid IN
(SELECT clid from lu_table_name
where ....)

Not sure exactly what you're trying to do with the count statement.

HTH

The above code would work; however i've got to pass the clids to a stored procedure(that contains more instructions etc.)
Mark

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-23 : 07:46:54


Declare @clid uniqueidentifier 
While (Select count(clid) from Table1) < 300 
Begin 
Execute sp_deleteclient @clid 
End

Does sp_deleteclient delete from Table1? If so, isn't this an infinate loop? I think you need to explain what going on in a little more detail . . .

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources