its an easy and or right... - 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 2005 Forums

Transact-SQL (2005)

its an easy and or right...

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2009-01-21 : 21:08:36

Trying to understand why i cant get the sp to search both the body and subject. Example, when I issue:

searchimlogs 'Duane Haas','','','','its back'

Its only searching the subject, when I want it to search both subject and body.


ALTER PROCEDURE [dbo].[searchimlogs]
               @sender      AS NVARCHAR(50)  = NULL,
               @begindate        AS smalldatetime  = NULL,
			   @enddate        AS smalldatetime  = NULL,
               @desintation AS NVARCHAR(50)  = NULL,
		       @message AS NVARCHAR(50)  = NULL
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET nocount on;
    
    SELECT *
    FROM   im_logs
    WHERE  (sender = @sender
             OR @sender IS NULL)
           AND (CONVERT(VARCHAR(30),DATE,101) between @begindate and @enddate
                 OR @begindate IS NULL)
           AND (destination LIKE '%' + @desintation + '%'
                 OR @desintation IS NULL)
		   AND (body LIKE '%' + @message + '%'
                 OR @message IS NULL)
           or (subject LIKE '%' + @message + '%'
                 OR @message IS NULL)
  END
GO

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-21 : 22:04:56

[code]
SELECT *
FROM im_logs
WHERE (sender = @sender
OR @sender IS NULL)
AND ([DATE] between @begindate and @enddate
OR @begindate IS NULL) --Don't convert the date to varchar
AND (destination LIKE '%' + @desintation + '%'
OR @desintation IS NULL)
AND (body LIKE '%' + @message + '%'
OR @message IS NULL)
AND (subject LIKE '%' + @message + '%'
OR @message IS NULL)

[/code]

Be One with the Optimizer
TG

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2009-01-21 : 22:36:46

quote:

Originally posted by TG


 SELECT *
    FROM   im_logs
    WHERE  (sender = @sender
             OR @sender IS NULL)
           AND ([DATE] between @begindate and @enddate
                 OR @begindate IS NULL) --Don't convert the date to varchar
           AND (destination LIKE '%' + @desintation + '%'
                 OR @desintation IS NULL)
		   AND (body LIKE '%' + @message + '%'
                 OR @message IS NULL)
           AND (subject LIKE '%' + @message + '%'
                 OR @message IS NULL)

Be One with the Optimizer
TG

when i use and, it doesnt return any results.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-21 : 23:27:38

Oh - I didn't notice that the last 2 conditions use the same parameter.
try this:

and (body like '%' + @message + '%' OR subject like '%' + @message + '%' OR @message is NULL )

Be One with the Optimizer
TG

karthickbabu
Posting Yak Master

151 Posts

Posted - 2009-01-22 : 00:21:08

SELECT * FROM im_logs WHERE
(sender = @sender OR @sender IS NULL) AND
(CONVERT(VARCHAR(30),DATE,101) between @begindate and @enddate OR @begindate IS NULL) AND
(destination LIKE '%' + @desintation + '%' OR @desintation IS NULL) AND
((body LIKE '%' + @message + '%' OR @message IS NULL) OR
(subject LIKE '%' + @message + '%' OR @message IS NULL))

====================================================
you realize you've made a mistake, take immediate steps to correct it.

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2009-01-22 : 08:48:14

quote:
Originally posted by TG

Oh - I didn't notice that the last 2 conditions use the same parameter.
try this:

and (body like '%' + @message + '%' OR subject like '%' + @message + '%' OR @message is NULL )

Be One with the Optimizer
TG

Not sure what to say, this isnt returning anything either???? When I do this

		   AND (body LIKE '%' + @message + '%'
                 OR @message IS NULL)
           or (subject LIKE '%' + @message + '%'
                 OR @message IS NULL)

it returns the subject lines that contain 'its back' and when i flip the two around:

		   AND (subject LIKE '%' + @message + '%'
                 OR @message IS NULL)
           or (body LIKE '%' + @message + '%'
                 OR @message IS NULL)

it returns the body that contains its back, but cant get both to be return???

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2009-01-22 : 08:57:33

nevermind, i wasnt passing in null for my other parameters, it works fine. THANKS TO EVERYONE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources