Build wherecondition based on input params

Hi .. can you explan little bit more..

why cant you..

where colname = @RemoteHost nvarchar(20) and ...

sorry


  IF (ISNULL(@From, 0) <> 0) and (ISNULL(@To, 0) <> 0)
    SELECT VisitTime, RemoteHost, SessionID, PageName, Referrer  FROM  dbo.tbl_VisitLog
    Where (VisitTime > (convert(datetime, convert(varchar, @From, 102))))
    And (VisitTime < (convert(datetime, convert(varchar, @To, 102))))
      AND RemoteHost = @RemoteHost AND SessionID = @SessionID AND Referrer = @Referrer AND PageName = @PageName
  Else
    SELECT VisitTime, RemoteHost, SessionID, PageName, Referrer  FROM  dbo.tbl_VisitLog
    Where (VisitTime > (convert(datetime, convert(varchar, getdate(), 102))))
      AND RemoteHost = @RemoteHost AND SessionID = @SessionID AND Referrer = @Referrer AND PageName = @PageName
GO

you want to check the nulls and put it into where condition?

Hi dishanf,

If any of the input params are null (the default value) then I dont want them in the wherecondition.
On the other hand if any of the input params aren't null I would like them in the wherecondition.

Sorry for not making this distinction :)

Using vba I would build a wherecondition part for each parameter, then join them all into one wherecondition.
Not sure how to do this with TSQL.



Alan

hey thanks fellas,

this is lookin real close to what I want except it returns records ouside the date range now.
I had to change the isnull truepart to '' avoid conversion nvarchar to int error.

This is what I have at the moment:

-- Testing
-- exec spReadVisitLog   -- returns all records for current day (default)
-- exec spReadVisitLog '23-feb-2004', '25-feb-2004' -- returns all records for 23-feb and 24-feb

-- exec spReadVisitLog '23-feb-2004', '25-feb-2004', '192.168.0.200'
-- should return all records for 23-feb and 24-feb with RemoteHost IP address = '192.168.0.200'



ALTER   PROCEDURE [dbo].[spReadVisitLog]
(
  @From datetime = null,
  @To datetime = null,
 
  @RemoteHost nvarchar(20) = null, -- use equals for wherecondition
  @SessionID nvarchar(50) = null,  -- use equals for wherecondition
  @Referrer nvarchar(255) = null,  -- use charindex(@Referrer) for wherecondition
  @PageName nvarchar(50) = null    -- use equals for wherecondition
)
AS
  IF (ISNULL(@From, 0) <> 0) and (ISNULL(@To, 0) <> 0)
    SELECT VisitTime, RemoteHost, SessionID, PageName, Referrer  FROM  dbo.tbl_VisitLog
    Where (VisitTime > (convert(datetime, convert(varchar, @From, 102))))
    And (VisitTime < (convert(datetime, convert(varchar, @To, 102))))
     AND
     ISNULL(@RemoteHost, '') = '' or ((ISNULL(@RemoteHost, '') <> '') AND (RemoteHost = @RemoteHost)) AND
     ISNULL(@SessionID, '') = '' OR ((ISNULL(@SessionID, '') <> '') AND (SessionID = @SessionID)) AND
     ISNULL(@Referrer, '') = '' OR ((ISNULL(@Referrer, '') <> '') AND (Referrer = @Referrer)) AND
     ISNULL(@PageName, '') = '' OR ((ISNULL(@PageName, '') <> '') AND (PageName = @PageName))
  Else
    SELECT VisitTime, RemoteHost, SessionID, PageName, Referrer  FROM  dbo.tbl_VisitLog
    Where (VisitTime > (convert(datetime, convert(varchar, getdate(), 102))))
     AND
     ISNULL(@RemoteHost, '') = '' OR ((ISNULL(@RemoteHost, '') <> '') AND (RemoteHost = @RemoteHost)) AND
     ISNULL(@SessionID, '') = '' OR ((ISNULL(@SessionID, '') <> '') AND (SessionID = @SessionID)) AND
     ISNULL(@Referrer, '') = '' OR ((ISNULL(@Referrer, '') <> '') AND (Referrer = @Referrer)) AND
     ISNULL(@PageName, '') = '' OR ((ISNULL(@PageName, '') <> '') AND (PageName = @PageName))
GO


exec spReadVisitLog '23-feb-2004', '25-feb-2004', '192.168.0.200' returns records like:
2004-02-20 21:04:31.000  192.168.0.200  782293029  /CashOz004Secure/home.asp  NULL
2004-02-20 21:04:33.000  192.168.0.200  782293029  /CashOz004Secure/homeabout.asp  NULL

Should only return records for 23-feb and 24-feb, this part works if I only pass @From and @To

Like: exec spReadVisitLog '23-feb-2004', '25-feb-2004'
2004-02-24 20:23:19.000      192.168.0.200      405659180      /CashOz004/home.asp      NULL
2004-02-24 20:23:24.000      192.168.0.200      405659180      /CashOz004/linkbanks.asp      NULL


Appreciations

Alan

(convert(datetime, convert(varchar, @From, 102))))

should be

(convert(datetime, convert(varchar, @From), 102)))
also for all the other date conversions

you are now converting the @From to a varchar(102) and then to a datetime without a conversion number
Maybe that will solve it

Hi p_sie,

I got the conversion from another q, it seemed to be working ok.
https://www.experts-exchange.com/questions/20895512/DateTime-conversion.html

Alan

This may help.

Table Script:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_VisitLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_VisitLog]
GO

CREATE TABLE [dbo].[tbl_VisitLog] (
      [VisitID] [int] IDENTITY (1, 1) NOT NULL ,
      [VisitTime] [datetime] NULL ,
      [RemoteHost] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
      [SessionID] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
      [AppRoot] [nvarchar] (40) COLLATE Latin1_General_CI_AS NULL ,
      [PageName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
      [Referrer] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
      [UserAgent] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
      [AdditionalText] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

Thank you

Alan,

you were right about the place of the 102 , learned something today, thank you!

About your problem:
Maybe the comparison of the dates is the problem
Where (VisitTime > (convert(datetime, convert(varchar, @From, 102))))

As I see in your output, Visttime = 2004-02-24 20:23:24.000    
But  (convert(datetime, convert(varchar, @From, 102))) = 24-02-2004 14:34:00
Different order in the day-month-year, so if you would convert both to the same, maybe it then works, because comparing different date-formats sometimes goes wrong

Thank you all, I can get some sleep now.

Goodnight and kindest regards

Alan

Hi fellas,

Just posted a modification request on this proc, if you are available please offer suggestions.

https://www.experts-exchange.com/questions/20897079/Modify-stored-proc-convert-null-to-'-'.html

Alan