Store Procedure - 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)

Store Procedure

Author

Topic

vicki
Posting Yak Master

117 Posts

Posted - 2002-11-22 : 17:09:13

Here what I have from my SP

=========================
Delete tbl
From tbl JOIN tbl1
On tbl.id = tbl1.id
Where tbl1.userid in (select userID from TEMP)
GO
=========================
how do i make stored proc run on more than 1 userid record at a time?

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-22 : 17:22:25

What you have now will delete more than one userID at a time. You'll need to explain exactly what you're trying to do in more detail if what you have does not do what you want.

Also, you've posted this same question in 2 other threads already. In the future, please stay with the original thread so that anyone that's trying to help can follow the question from the beginning.

mdanwerali
Starting Member

30 Posts

Posted - 2002-11-23 : 03:24:27

u have already posted to this thread. and i written the answer check it out.
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=21854

quote:

What you have now will delete more than one userID at a time. You'll need to explain exactly what you're trying to do in more detail if what you have does not do what you want.

Also, you've posted this same question in 2 other threads already. In the future, please stay with the original thread so that anyone that's trying to help can follow the question from the beginning.

vicki
Posting Yak Master

117 Posts

Posted - 2002-11-25 : 09:14:19

Thanks

I want to make the stored proc run on more than 1 user at a time

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-25 : 09:51:57

Does that mean you want to pass a list of userID's you want deleted and have the procedure delete only those ID's in the list?

If yes, search SQL Team for "Dynamic SQL" and also for "CSV", you'll find a number of articles that demonstrate how to do this.

vicki
Posting Yak Master

117 Posts

Posted - 2002-11-25 : 10:42:46

Thanks

Not really, here is my correct query:

CREATE PROCEDURE V_Del
@userID Integer
AS

Delete tbl
From tbl JOIN tbl1
On tbl.id = tbl1.id
Where tbl1.userid =@userID
GO

==========
I want to put this inside a loop to make this del query run more than one time at the time but don't know the sysntax:

Something like this, but i don't know for sure:

Open cusor on @userID's
loop
calls V-Del @userID
.....next from cusor
end loop
close cusor

I don't know much about the sysntax
Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-25 : 11:15:01

this is for @userIDs something like '1,2,3,20,36'

set nocount on
declare @userIDs varchar(20)
declare @i int, @j int, @id int
declare @s varchar(10)

select @i = 0, @j = 0
while @i < len(@userIDs)
begin
select @j = charindex(',',@userIDs,@i+1)
if @j = 0
select @j = len(@userIDs)+ 1
select @id = convert(int,substring(@userIDs,@i + 1,@j-@i-1))
exec V-Del @id
select @i = @j
end

or you could just put these into a temp table and do the delete from there.
If you do it in a loop consider putting it inside a transaction with error processing.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vicki
Posting Yak Master

117 Posts

Posted - 2002-11-25 : 11:24:10

Thanks alot

I never do cursor before , and I don't really understand your statement, coupld you please expalin to me

Thanks alot

vicki
Posting Yak Master

117 Posts

Posted - 2002-11-25 : 11:47:00

Hi,

Here what I have the sysntax for cursor refrence on my query but i don't know I have the correct sysntax or not, please help

OPEN V_Del Cursor
FETCH NEXT FROM V_Del
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM V_Del
END
CLOSE V_Del
DEALLOCATE V_Del

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-25 : 12:01:14

Vicki-

You don't need a cursor for this procedure. In any case, you cannot open a cursor on a stored procedure, especially one that only performs a DELETE operation.

The code Nigel provided does what *I THINK* you want to do, which is delete rows matching multiple user IDs. You haven't clarified if that is what you want to do or not. If you need to DELETE these user ID's from one table, then it works as is, and you can even re-write your stored procedure to incoporate his code in one operation. If you need to DELETE them from multiple tables, it can be easily modified to do so.

And you can do this even more easily using dynamic SQL or the CSV methods I indicated earlier. I don't understand why you keep discussing the cursor, you don't need it, and it will perform far worse than every other method listed in this thread (and the others)

Can you clarify any of this for us? Just describe what you want to do in the simplest terms, and provide an example of how you want to be able to do it.

Edited by - robvolk on 11/25/2002 12:02:23

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-25 : 13:00:56

To see what the code does run the code below and look up charindex and substring. It just runs a loop through the input string finding all the IDs and calling the SP for each one.
As Rob points out if you just want to delete from the table and have this input csv string you don't need to call the SP for each ID but can do it in one statement

delete tbl
where ',' + @userIDs + ',' like '%,' + convert(varchar(10),tbl.id) + ',%'

set nocount on
declare @userIDs varchar(20)
select @userIDs = '1,2,3,20,36'
declare @i int, @j int, @id int
declare @s varchar(10)

select @i = 0, @j = 0
while @i < len(@userIDs)
begin
select @j = charindex(',',@userIDs,@i+1)
if @j = 0
select @j = len(@userIDs)+ 1
select @id = convert(int,substring(@userIDs,@i + 1,@j-@i-1))
--exec V-Del @id
select 'calling V-Del with ' + convert(varchar(10),@id) + ' @i = ' + convert(varchar(10),@i) + ' @j = ' + convert(varchar(10),@j)
select @i = @j
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vicki
Posting Yak Master

117 Posts

Posted - 2002-11-25 : 13:01:24

Thanks so much for your input. To be honesly, I don't much about the loop or cusor. After I created that SP, my project leader ask me to do a cursor or loop to make that query run more than one at the time. He wants to able to excute this SP to delete the @visitID more than one at the time.
At the begining i have Where tbl1.userid in (select userID from TEMP)but he said it can't be because if so then db will take more time to run it, so he want to replace that with @userID and but this SP in loop to able to run one at time but keep go through the loop. I don't know how to do it, because I am new with this, please help
thanks

vicki
Posting Yak Master

117 Posts

Posted - 2002-11-25 : 13:05:49

Thanks

UserId is not on CSV format, it's on the TEMP table, then how can I modify your statement?

Thanks again

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-25 : 13:16:13

declare @id int, @maxid int
select @id = 0, @maxid = max(id) from TEMP

while @id < @maxid
begin
select @id = min(id) from TEMP where id > @id
exec spDelete @id
end

or maybe
declare @id int
while exists (select * from TEMP)
begin
select @id = min(id) from TEMP
exec spDelete @id
delete TEMP where id = @id
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vicki
Posting Yak Master

117 Posts

Posted - 2002-11-25 : 13:56:33

Thanks

ok, my main question is how can i have the v_del SP inside the loop to make the delete query run more than one at the time?

vicki
Posting Yak Master

117 Posts

Posted - 2002-11-25 : 14:07:28

How do I make this SPs so it would delete more than 1 VisitID each execution:

CREATE PROCEDURE Delete_All_VisitID
@id int, @maxid int
As
select @id = 0, @maxid = max(id) from TempTable_VisitID

while @id @id

exec visit_Del @id
end

//Below is the Visit_Del SP

CREATE PROCEDURE Visit_Del
@VisitId Int
AS

Delete UserClinicalDocumentViewer
From UserClinicaldocumentViewer ucdv JOIN Message m
On ucdv.Messageid = m.messageid
Where m.visitid = @visitid

Delete queuedMessagePassThruPrint
From queuedMessagePassThruPrint qmptp JOIN Message m
On qmptp.Messageid = m.messageid
Where m.visitid = @visitid

Delete MessageText
From MessageText mt JOIN Message m
On mt.Messageid = m.messageid
Where m.visitid = @visitid

Delete MessagePhysicianRole
From MessagePhysicianRole mpr JOIN Message m
On mpr.Messageid = m.messageid
Where m.visitid = @visitid

Delete Message where visitid = @visitid

Delete VisitPhysicianRole
where visitid = @visitid

Delete Visit where visitid = @visitid

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources