A DB block during stored procedure excecution...

Hi all,
I'd like to ask if is there any deference between executing a stored
procedure with 'exec' command in the Query analyzer and executing the code o
f
the stored procedure in Query analyzer too. I mean that I copied the source
of stored procedure in a window of the analyzer, I declare the parameters of
stored procedure as variables and I set the same values into them and I just
run the source...
When I execute the stored proc by "exec sp 'x', 'y', 'z' " where x,y,z are
the parameters sometimes it occures a block... When I run the source
declaring the parameters as variables etc, all are fine and I have never any
block... How is it possible? Is there any idea?
Thanks in advance..Christos,
What do you mean by block?
See if this helps:
[url]http://groups-beta.google.com/group/microsoft.public.sqlserver.server/msg/a5517668
94ed8781? q=%22what+is%22%2B%22parameter+sniffing%
22&hl=en&lr=&ie=UTF-8&rnum=1[/url
]
AMB
"Christos" wrote:

> Hi all,
> I'd like to ask if is there any deference between executing a stored
> procedure with 'exec' command in the Query analyzer and executing the code
of
> the stored procedure in Query analyzer too. I mean that I copied the sourc
e
> of stored procedure in a window of the analyzer, I declare the parameters
of
> stored procedure as variables and I set the same values into them and I ju
st
> run the source...
> When I execute the stored proc by "exec sp 'x', 'y', 'z' " where x,y,z are
> the parameters sometimes it occures a block... When I run the source
> declaring the parameters as variables etc, all are fine and I have never a
ny
> block... How is it possible? Is there any idea?
> Thanks in advance..
>|||I mean that stored procedure never ends, so it is blocking other users from
using the same tables etc (the sp updates data in some tables...)|||Christos,
If the sp never ends could be because another process is blocking the
resources needed by the sp and it is waiting, or the workload is heavy.
You can use EM (Management - Current Activity) or execute sp_who2 from QA to
see the processes and locks. You can also use Profiler to trace locks.
AMB
"Christos" wrote:

> I mean that stored procedure never ends, so it is blocking other users fro
m
> using the same tables etc (the sp updates data in some tables...)|||The strange in this situation is that when I have a block during the sp
execution, if I kill the process of sp and I try again to run it separetely
with the same parameters in the Q Analyzer's environment, the blocking
happens again. If I 'export' the source code in the analyzer's window and ru
n
it again using the parameters as variables it finishes imediatelly without
any problem...sql