SP returns too many records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SP returns too many records

Author

Topic

yxiong
Starting Member

4 Posts

Posted - 2002-04-01 : 10:51:00

Dear All,

I have a stored procedure that is supposed to return a group of records that contain a user input within a given date range. Here is the code for the sp:

CREATE PROCEDURE dbo.CB_Ret_Search
@p4 varchar(50),
@p7 datetime,
@p8 datetime

AS

BEGIN

SELECT *
FROM Details
WHERE
Challenge LIKE '%' + @p4 + '%' OR
Act LIKE '%' + @p4 + '%'
AND SubDate BETWEEN @p7 and @p8
AND DelFlag = '0'

END
GO

The results are intermittent at best. For example, in most cases this sp will return all records containing the keyword without regard to the date range.

I have been able to get the desired results with separate sp, but I would like to make this work together in one sp.

Any help would be greatly appreciated!

--YXiong

dsdeming

479 Posts

Posted - 2002-04-01 : 10:56:43

Does it help to try this:

SELECT *
FROM Details
WHERE SubDate BETWEEN @p7 and @p8
AND DelFlag = '0'
AND ( Challenge LIKE '%' + @p4 + '%' OR
Act LIKE '%' + @p4 + '%' )

I always look closely at where clauses with ORs in them. Well-placed parentheses often fix the problem.

yxiong
Starting Member

4 Posts

Posted - 2002-04-02 : 16:42:10

Thank you I will try this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources