trans53 (UseLinkToEmail@dbForumz.com) writes:
> I have very interesting problem:
>
> Our java application(websphere server) calling stored procedure where
> we can pass the paramater or not.When we pass the parameter we can see
> the result very fast,but when we pass NULL to the stored procedure
> nothing returns and we have to kill connection.I noticed in Profiler
> that when i kill connection immediatelly after we have RPC:Completed
> for the that stored procedure.
>
> The same stored procedure runs with no problem in Query Analizer and
> returns data in from 0 to 10 sec even if we pass parameter or not.
>
> what can be the problem in this situation? Thanks

My guess is that the procedure includes something like:

IF @param IS NULL
SELECT @param = getdate()

SQL Server builds the query plan using the input value of the parameter
as a guess. When you change the parameter, you lead SQL Server astray.
Try copying the input parameter to a local variable, and then use
that in the query instead. Since SQL Server does not know about the
value of local parameters, it will use a standard assumption.

The reason in works in QA but not Java, is that they have different
query plan. This is because in QA, ARITHABORT is ON by default, which
it's not likely to be from Java.

If this attempt to guesswork did not address the issue, then you need
to supply some more information. For instance, the procedure code...

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

"Erland Sommarskog2" wrote:
> trans53 (UseLinkToEmail@dbForumz.com) writes:
> > I have very interesting problem:
> >
> > Our java application(websphere server) calling stored
> procedure where
> > we can pass the paramater or not.When we pass the parameter
> we can see
> > the result very fast,but when we pass NULL to the stored
> procedure
> > nothing returns and we have to kill connection.I noticed in
> Profiler
> > that when i kill connection immediatelly after we have
> RPC:Completed
> > for the that stored procedure.
> >
> > The same stored procedure runs with no problem in Query
> Analizer and
> > returns data in from 0 to 10 sec even if we pass parameter
> or not.
> >
> > what can be the problem in this situation? Thanks
>
> My guess is that the procedure includes something like:
>
> IF @param IS NULL
> SELECT @param = getdate()
>
> SQL Server builds the query plan using the input value of the
> parameter
> as a guess. When you change the parameter, you lead SQL Server
> astray.
> Try copying the input parameter to a local variable, and then
> use
> that in the query instead. Since SQL Server does not know
> about the
> value of local parameters, it will use a standard assumption.
>
> The reason in works in QA but not Java, is that they have
> different
> query plan. This is because in QA, ARITHABORT is ON by
> default, which
> it's not likely to be from Java.
>
> If this attempt to guesswork did not address the issue, then
> you need
> to supply some more information. For instance, the procedure
> code...
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp

Erland,Thanks a lot for reply.

We modified the SP and will implement on monday.If we still have a
problem on monday i wil post a code for the SP.

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Disc...ict232911.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=809314