Stored Procedure problems

Question

Post reply

Stored Procedure problems

M Joomun

SSCrazy

Points: 2018
More actions
September 6, 2010 at 6:20 am

#157184

I have the following stored procedure which I'm calling from a Windows Forms app using VB.NET. I'm fairly certain that the problem is not in the app, but rather in the procedure. The procedure has two criteria and the application user should be able to enter either one or both criteria to search the database. By clicking on a button, the user is returned the results of the query.
The SQL I've posted below for example will only work for the date criteria (dateassayrequested). I don't get any results if the user enters assayid or assayid and dateassayrequested. However, if I remove the @dateassayrequested criteria from the query and use only @assayid, I get the expected results!
So the issue is that I get one or other of the criteria to work but can only get the correct results for assayid if it is the only criteria in the procedure. If I have both criteria in the procedure and the user searches by dateassayrequested, I get the correct results. But no results if assayid forms part of the search, either on it's own or in conjunction with dateassayrequested.
Does anyone know where I've gone wrong?
Thanks.
@assayid int,
@dateassayrequested datetime
AS
SELECT assayid, DateAssayRequested, LabNo, ReasonRepeat,
PersonReq, RepeatedYN, DateRepeated,
DtRepeatDy, PersonRepeat, ReasonNotRepeated, LIMSUpdateYN
FROM tblassay
WHERE (@assayID = 0 OR assayid = @assayid)
AND (@dateassrequested IS NULL OR dateassayrequested = DATEADD(dd, DATEDIFF(d,0,@dateassayrequested), 0))

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

crazy4sql SSCoach Points: 19590 More actions · Answer 1

i think it could be because of :-

(@assayID = 0 OR assayid = @assayid).

can you set the @assayid = 0 on top and then @assayid = whatever passed.

In this way in your where clause it will be assayid = @assayid.

Also same for date related paramaeter.

----------
Ashish

M Joomun SSCrazy Points: 2018 More actions · Answer 2

ashish.kuriyal (9/6/2010)
i think it could be because of :-
(@assayID = 0 OR assayid = @assayid).
can you set the @assayid = 0 on top and then @assayid = whatever passed.
In this way in your where clause it will be assayid = @assayid.
Also same for date related paramaeter.

Thanks very much for the response Ashish.

Sorry, but I'm not sure I understand what you mean by 'can you set the @assayid = 0 on top and then @assayid = whatever passed'. Can you elaborate please?

crazy4sql SSCoach Points: 19590 More actions · Answer 3

i mean something like :-

@assayid int,

@dateassayrequested datetime

AS

if @assayid <>0 set @assayid = 0

and then your select statement

----------
Ashish

M Joomun SSCrazy Points: 2018 More actions · Answer 4

ashish.kuriyal (9/6/2010)
i mean something like :-
@assayid int,
@dateassayrequested datetime
AS
if @assayid <>0 set @assayid = 0
and then your select statement

That makes absolutely no difference I'm afraid.

crazy4sql SSCoach Points: 19590 More actions · Answer 5

ok, next try then

dateassayrequested = DATEADD(dd, DATEDIFF(d,0,@dateassayrequested), 0))
[/code]

is this perticualr code working ok?

because when I replaced your variable with current date, then I am getting as below result :-

getdate() is Sep 6 2010 2:10PM

and

DATEADD(dd, DATEDIFF(d,0,@dateassayrequested), 0) is Sep 6 2010 12:00AM

So is it the expected result?

----------
Ashish

M Joomun SSCrazy Points: 2018 More actions · Answer 6

ashish.kuriyal (9/6/2010)
ok, next try then
dateassayrequested = DATEADD(dd, DATEDIFF(d,0,@dateassayrequested), 0))
[/code]
is this perticualr code working ok?
because when I replaced your variable with current date, then I am getting as below result :-
getdate() is Sep 6 2010 2:10PM
and
DATEADD(dd, DATEDIFF(d,0,@dateassayrequested), 0) is Sep 6 2010 12:00AM
So is it the expected result?

Yes, that returns the expected results.

crazy4sql SSCoach Points: 19590 More actions · Answer 7

ok, and this is the only code you have behind that perticular functionality or some other set of code as well?

Not able to find any specific problem with this set of code.

Can you try to run the seleect statement in query window and put the same value in where condition which you passing via parameter. Are you getting any result back then?

----------
Ashish

Abhijit More SSCertifiable Points: 6897 More actions · Answer 8

okay just putting my understanding what you are looking for is

If @assayid int > Then search by @assayid

and

If @dateassayrequested datetime is Valid date Then search by datediff

If this is the case then you can write the query as below..

Hold on! make sure to keep both input parameters NULLABLE

@assayid int = NULL,

@dateassayrequested datetime = NULL

AS

SELECT assayid, DateAssayRequested, LabNo, ReasonRepeat,

PersonReq, RepeatedYN, DateRepeated,

DtRepeatDy, PersonRepeat, ReasonNotRepeated, LIMSUpdateYN

FROM tblassay

WHERE (assayid = COALESCE(@assayid, assayid)

AND ( @dateassayrequested IS NULL OR dateassayrequested = DATEADD(dd, DATEDIFF(d,0,@dateassayrequested), 0) )

Abhijit - http://abhijitmore.wordpress.com

M Joomun SSCrazy Points: 2018 More actions · Answer 9

Abhijit More (9/6/2010)
okay just putting my understanding what you are looking for is
If @assayid int > Then search by @assayid
and
If @dateassayrequested datetime is Valid date Then search by datediff
If this is the case then you can write the query as below..
Hold on! make sure to keep both input parameters NULLABLE
@assayid int = NULL,
@dateassayrequested datetime = NULL
AS
SELECT assayid, DateAssayRequested, LabNo, ReasonRepeat,
PersonReq, RepeatedYN, DateRepeated,
DtRepeatDy, PersonRepeat, ReasonNotRepeated, LIMSUpdateYN
FROM tblassay
WHERE (assayid = COALESCE(@assayid, assayid)
AND ( @dateassayrequested IS NULL OR dateassayrequested = DATEADD(dd, DATEDIFF(d,0,@dateassayrequested), 0) )

Thanks for the help.

The situation is exactly as in the original post. I get no results back if I search by assay id, but do get the correct result for dateassayrequested.

Abhijit More SSCertifiable Points: 6897 More actions · Answer 10

try this...

select * from Purchasing.PurchaseOrderDetail

where case @OrderQty when 0 then OrderQty else @OrderQty end = OrderQty

and (@duedate IS NULL OR duedate = DATEADD(dd, DATEDIFF(d,0,@duedate), 0))

Abhijit - http://abhijitmore.wordpress.com

Abhijit More SSCertifiable Points: 6897 More actions · Answer 11

try this....

select * from Purchasing.PurchaseOrderDetail

where case @OrderQty when 0 then OrderQty else @OrderQty end = OrderQty

and (@duedate IS NULL OR duedate = DATEADD(dd, DATEDIFF(d,0,@duedate), 0))

Abhijit - http://abhijitmore.wordpress.com

M Joomun SSCrazy Points: 2018 More actions · Answer 12

Abhijit More (9/6/2010)
try this....
select * from Purchasing.PurchaseOrderDetail
where case @OrderQty when 0 then OrderQty else @OrderQty end = OrderQty
and (@duedate IS NULL OR duedate = DATEADD(dd, DATEDIFF(d,0,@duedate), 0))

Thanks again.

There's no change. If the query contains only one criteria (either assayid or dateassayrequested) then I get the expected results. What I can't do is search using both criteria or using assayid if my procedure includes the second criteria.

I'm going to try a procedure where there is a second criteria, but it is not a date.

<ED>

I've tried the query with two criteria. One is int datatype and the second is varchar. It now works as expected (I can search on either or both criteria, which is what I can't do if there I include the date criteria in my procedure!)

Any ideas why the date criteria is causing this problem?

</ED>

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 13

What is the data type of your duedate column? If it is a datetime - are the dates always at midnight, or will they have a time?

Most likely, the reason this is not working is because the duedate has time - and you are comparing to midnight which is not equal.

Try changing it to:

Declare @startDate datetime

,@endDate datetime;

Set @startDate = dateadd(day, datediff(day, 0, @dateassayrequested), 0);

Set @endDate = dateadd(day, 1, @startDate);

Select *

From Purchasing.PurchaseOrderDetail

Where (@assayid Is Null Or assayid = @assayid)

And (@dateassayrequested Is Null

Or (dateassayrequested >= @startDate

And dateassayrequested < @endDate))

Now, you should know that the above will not generate the best execution plan. Your best option would be to use dynamic SQL and build the statement based upon the input parameters. There are plenty of discussions on this forum about this.

Gail Shaw blogs about this at: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

M Joomun SSCrazy Points: 2018 More actions · Answer 14

Jeffrey Williams-493691 (9/6/2010)
What is the data type of your duedate column? If it is a datetime - are the dates always at midnight, or will they have a time?
Most likely, the reason this is not working is because the duedate has time - and you are comparing to midnight which is not equal.
Try changing it to:
Declare @startDate datetime
,@endDate datetime;
Set @startDate = dateadd(day, datediff(day, 0, @dateassayrequested), 0);
Set @endDate = dateadd(day, 1, @startDate);
Select *
From Purchasing.PurchaseOrderDetail
Where (@assayid Is Null Or assayid = @assayid)
And (@dateassayrequested Is Null
Or (dateassayrequested >= @startDate
And dateassayrequested < @endDate))
Now, you should know that the above will not generate the best execution plan. Your best option would be to use dynamic SQL and build the statement based upon the input parameters. There are plenty of discussions on this forum about this.
Gail Shaw blogs about this at: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Thanks very much for the reply. I'll give this a go and also have a look at the blog you mentioned.