Posted by Erland Sommarskog on 06/18/05 01:02

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/techinfo/productdoc/2000/books.asp

[Back to original message]