Local variables in stored procedures

Hi!
I'm using SQL Server 7.0 and it is a multiuser application.
My problem is that sometimes during parallel calls to a stored procedure it
produces wrong results. It takes between 5 to 20 sec to execute the stored
procedure.
To be able to know why the result sometimes is wrong I will log some values
from local variables.
My guess is that my part-result from some lookups are overwritten.
But until I get enough log-results to analyse I have a couple of questions:
- Are local variables overwritten by another call to the same procedure?
- Can I save part-result in a more secure way? I still want to have the
possibilty to call the procedure in a parallel way.
- As a last option. Is there a simple way to forbid parallel calls to a
procedure? (I have read some about "set transaction isolation level
serializable" but I'm afraid it has too large impact on other calls that
questions the same tables that are in use in the stored procedure)
Hope someone has a godd answer to give.
Best regards
SvenneSvenne
Can you show us your SP's call and how do you handle local variables within
SP?
If you have SELECT/UPDATE/DELETE/INSERT operations within a SP try to wrap
it into BEGIN TRAN ...COMMIT commands
"Svenne" <sasodergren@.hotmail.com> wrote in message
news:%23Y0MMBp7FHA.2384@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I'm using SQL Server 7.0 and it is a multiuser application.
> My problem is that sometimes during parallel calls to a stored procedure
> it produces wrong results. It takes between 5 to 20 sec to execute the
> stored procedure.
> To be able to know why the result sometimes is wrong I will log some
> values from local variables.
> My guess is that my part-result from some lookups are overwritten.
> But until I get enough log-results to analyse I have a couple of
> questions:
> - Are local variables overwritten by another call to the same procedure?
> - Can I save part-result in a more secure way? I still want to have the
> possibilty to call the procedure in a parallel way.
> - As a last option. Is there a simple way to forbid parallel calls to a
> procedure? (I have read some about "set transaction isolation level
> serializable" but I'm afraid it has too large impact on other calls that
> questions the same tables that are in use in the stored procedure)
> Hope someone has a godd answer to give.
> Best regards
> Svenne
>