multiple deletes with a stored procedure

[Lauren Quantrell]

Just wondering if this is good form:


Alter Procedure "mySPName"
@UniqueID int
AS
set nocount on
set xact_abort off

DELETE FROM tblNameOne
WHERE
(tblNameOne.Uni queID = @UniqueID)

DELETE FROM tblNameTwo
WHERE
(tblNameTwo.Uni queID = @UniqueID)


Is it a good idea to run multiple detele statements within one SP?
thanks,
lq

[Hugo Kornelis]

Re: multiple deletes with a stored procedure

On 8 Apr 2004 12:30:55 -0700, Lauren Quantrell wrote:
[color=blue]
>Just wondering if this is good form:[/color]

Looks fine to me, except I'd prefer set xact_abort on. But that's a
general comment, your situation mught demand this option to be off.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Lauren Quantrell]

Re: multiple deletes with a stored procedure

Thanks,
I am having a situation were the SQL server experiences every now and
then excessive blocking errors, sometimes around the time of execution
of this type of stored procedure, sometimes around the time of an SP
where I'm running multiple INSERT queries with one SP. I'm trying to
identify where the problem may be.

I'm wondering if you could tell me what the use of GO or RETURN is and
if I need them in this sort of SP?
lq


Hugo Kornelis <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message news:<v1bb70td5 7mtifd8le40ot4k v9gp118as9@4ax. com>...[color=blue]
> On 8 Apr 2004 12:30:55 -0700, Lauren Quantrell wrote:
>[color=green]
> >Just wondering if this is good form:[/color]
>
> Looks fine to me, except I'd prefer set xact_abort on. But that's a
> general comment, your situation mught demand this option to be off.
>
> Best, Hugo[/color]

[Hugo Kornelis]

Re: multiple deletes with a stored procedure

On 9 Apr 2004 06:42:55 -0700, Lauren Quantrell wrote:
[color=blue]
>Thanks,
>I am having a situation were the SQL server experiences every now and
>then excessive blocking errors, sometimes around the time of execution
>of this type of stored procedure, sometimes around the time of an SP
>where I'm running multiple INSERT queries with one SP. I'm trying to
>identify where the problem may be.[/color]

I don't know much about blocking. The only thing I know is that you
can use sp_who to identify which process all other processes are
waiting for. The only action I have ever taken in these situations was
to either kill the blocking process or tell the complaining users that
this process was too important to postpone until the evening.

If you find a procedure like this to be the cause of blocking, there
are probably ways to improve this. However, I don't know how to do
that. Maybe you should ask a new question, making sure that "blocking"
is in the subject line. Also, note that there are a lot of groups
devoted to SQL Server in the microsoft.publi c.sqlserver hierarchy. A
question about blocking could go in either .programming or .server.

[color=blue]
>I'm wondering if you could tell me what the use of GO or RETURN is and
>if I need them in this sort of SP?[/color]

RETURN is used to exit immediately from a stored procedure or trigger.
Check Books Online for more detailed description and examples. Use it
if you detect a situation where the remaining statements in the
procedure should not be executed.

GO means "end of batch". It is intercepted by Query Analyzer (as well
as OSQL, ISQL and probably other tools as well) and prompts them to
send everything to the server. Therefor, you can't put GO inside a
procedure. Example:

Create procedure Testit
as
select * from sysobjects
go
select * from sysfiles
go

Execute this, and all rows in sysfiles will be listed. Next, execute
"sp_helptex t Testit" and you'll see that only the select from
sysobjects made it into the procedure. The other select was sent as a
seperate batch and therefor executed immediately.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[HoustonLucifer]

Re: multiple deletes with a stored procedure

If i am not wrong, i think the GO statment is used for Batch Termination.
Some of the SQL Statments don't allow some Commands to be executed along
with DDL. So the word GO can be used to tell the SQL Server that one batch
finished and the other batch is ready. The return statement can be used when
checking for error codes and returning an error code(from a procedure) to
the appropriate procedure/SQL.

"Lauren Quantrell" <laurenquantrel l@hotmail.com> wrote in message
news:47e5bd72.0 404090542.db112 d9@posting.goog le.com...[color=blue]
> Thanks,
> I am having a situation were the SQL server experiences every now and
> then excessive blocking errors, sometimes around the time of execution
> of this type of stored procedure, sometimes around the time of an SP
> where I'm running multiple INSERT queries with one SP. I'm trying to
> identify where the problem may be.
>
> I'm wondering if you could tell me what the use of GO or RETURN is and
> if I need them in this sort of SP?
> lq
>
>
> Hugo Kornelis <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message[/color]
news:<v1bb70td5 7mtifd8le40ot4k v9gp118as9@4ax. com>...[color=blue][color=green]
> > On 8 Apr 2004 12:30:55 -0700, Lauren Quantrell wrote:
> >[color=darkred]
> > >Just wondering if this is good form:[/color]
> >
> > Looks fine to me, except I'd prefer set xact_abort on. But that's a
> > general comment, your situation mught demand this option to be off.
> >
> > Best, Hugo[/color][/color]

[William Cleveland]

Re: multiple deletes with a stored procedure

Have you looked at triggers? We've got an idiot vendor (who should
be gone by the end of the year) who implemented their referential
integrity with triggers, rather than real foreign keys.
We make up for that with an excessive use of cursors and nolock
hints, but if you have the option of actually fixing the trigger,
it would be better.

Bill



Lauren Quantrell wrote:
[color=blue]
> Thanks,
> I am having a situation were the SQL server experiences every now and
> then excessive blocking errors, sometimes around the time of execution
> of this type of stored procedure, sometimes around the time of an SP
> where I'm running multiple INSERT queries with one SP. I'm trying to
> identify where the problem may be.
>
> I'm wondering if you could tell me what the use of GO or RETURN is and
> if I need them in this sort of SP?
> lq
>
>
> Hugo Kornelis <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message news:<v1bb70td5 7mtifd8le40ot4k v9gp118as9@4ax. com>...
>[color=green]
>>On 8 Apr 2004 12:30:55 -0700, Lauren Quantrell wrote:
>>
>>[color=darkred]
>>>Just wondering if this is good form:[/color]
>>
>>Looks fine to me, except I'd prefer set xact_abort on. But that's a
>>general comment, your situation mught demand this option to be off.
>>
>>Best, Hugo[/color][/color]