Trying to create a search using comments

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 2005 Forums

Transact-SQL (2005)

Trying to create a search using comments

Author

Topic

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-12-12 : 14:59:26

Hi all I am trying to make it so that users can search the comments/reasons. I have a stored procedure thats not giving me the results I'm looking for. They need to be able to search by IssueDate, Duration and Reason.
This will only let me search by some of the parameters liek the issue date, but not the duration or the reason. This is probably not a good idea but It was request from a database I dont really have anything to do with. I must be missing something, they need to be able to search by either or and sometimes all.

ALTER PROCEDURE [dbo].[SearchByDateLstNmDuration]
@StartIssueDate datetime = null,
@EndIssueDate datetime = null,
@Enter_Duration nvarchar(10)  = null,
@Enter_Reason_for_Exclusion nvarchar(100)
AS 
SELECT      IR#, [Issue Date], [Reason for Exclusion], Duration, [First Name], [Last Name]
FROM          [dbo].[Extended Exclusions]
WHERE  (@StartIssueDate is null or [Issue Date] >= @StartIssueDate) 
AND (@EndIssueDate is null or [Issue Date] <= @EndIssueDate)
AND (@Enter_Duration is null or Duration = @Enter_Duration)
AND([Reason for Exclusion] like @Enter_Reason_for_Exclusion + '%')

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-12-12 : 15:31:00

AND([Reason for Exclusion] like '%' + @Enter_Reason_for_Exclusion + '%')

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-12-15 : 13:19:45

the only problem is that it will only give me the [Reason for Exclusion] results and thats all. It wont give me @StartIssueDate and @EndIssueDate or @Enter_Duration??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 13:23:52

so is this what you want?


ALTER PROCEDURE [dbo].[SearchByDateLstNmDuration]
@StartIssueDate datetime = null,
@EndIssueDate datetime = null,
@Enter_Duration nvarchar(10)  = null,
@Enter_Reason_for_Exclusion nvarchar(100)=null
AS 
SELECT      IR#, [Issue Date], [Reason for Exclusion], Duration, [First Name], [Last Name]
FROM          [dbo].[Extended Exclusions]
WHERE  (@StartIssueDate is null or [Issue Date] >= @StartIssueDate) 
AND (@EndIssueDate is null or [Issue Date] <= @EndIssueDate)
AND (@Enter_Duration is null or Duration = @Enter_Duration)
AND([Reason for Exclusion] like '%'+ @Enter_Reason_for_Exclusion + '%')

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-12-15 : 13:36:09

That is exactly what I have, and it is still not executing correctly

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-12-15 : 14:14:05

now if I do it like this I get everything but the @Enter_Reason_for_Exclusion

ALTER PROCEDURE [dbo].[SearchByDateLstNmDuration]
@StartIssueDate datetime = null,
@EndIssueDate datetime = null,
@Enter_Duration nvarchar(10)  = null,
@Enter_Reason_for_Exclusion nvarchar(100)= null
AS 
SELECT      IR#, [Issue Date], [Reason for Exclusion], Duration, [First Name], [Last Name]
FROM          [dbo].[Extended Exclusions]
WHERE  (@StartIssueDate is null or [Issue Date] >= @StartIssueDate) 
AND (@EndIssueDate is null or [Issue Date] <= @EndIssueDate)
AND (@Enter_Duration is null or Duration = @Enter_Duration)
or([Reason for Exclusion] like '%'+ @Enter_Reason_for_Exclusion + '%')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 00:11:55

quote:

Originally posted by muzzettemm

now if I do it like this I get everything but the @Enter_Reason_for_Exclusion

ALTER PROCEDURE [dbo].[SearchByDateLstNmDuration]
@StartIssueDate datetime = null,
@EndIssueDate datetime = null,
@Enter_Duration nvarchar(10)  = null,
@Enter_Reason_for_Exclusion nvarchar(100)= null
AS 
SELECT      IR#, [Issue Date], [Reason for Exclusion], Duration, [First Name], [Last Name]
FROM          [dbo].[Extended Exclusions]
WHERE  (@StartIssueDate is null or [Issue Date] >= @StartIssueDate) 
AND (@EndIssueDate is null or [Issue Date] <= @EndIssueDate)
AND (@Enter_Duration is null or Duration = @Enter_Duration)
or([Reason for Exclusion] like '%'+ @Enter_Reason_for_Exclusion + '%')

whats the purpose of or? shouldnt it be

AND([Reason for Exclusion] like '%'+ @Enter_Reason_for_Exclusion + '%' or @Enter_Reason_for_Exclusion is null)

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-12-16 : 13:44:36

YOUR A GENIUS VISAKH16 I was wondering if I needed to add the Is null to the Reason_for_Exclusion but I wasnt sure how. Thank you so much I appreciate it and I've learned something new

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 14:10:47

no problem...

you're welcome

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-12-16 : 14:20:25

I think parameter stored procedures are my favorite because then the users can choose what values they want for themselves

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 14:22:39

quote:
Originally posted by muzzettemm

I think parameter stored procedures are my favorite because then the users can choose what values they want for themselves

yup...and they are much better than using dynamic sql queries as they reduce sql injection attacks.

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-12-16 : 16:45:40

Oh wow thats cool I didnt know that

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 23:41:53

cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources