Problem with DateTime and strings in stored procedures

[antao]

I'm keeping in the database a log of all the sessions for my
application. I'm trying to write a stored procedure that returns all
the sessions that; the login contains a certain string, loggedin after
a certain datetime and loggedout before another datetime. Any
combination of these parameters can be used and, if none, returns all
the log.

Below is the code I came up with but I'm having a "Syntax error
converting datetime from character string" exception. When not using
DateTime parameters everything works fine. Can you tell me how can I
avoid this exception? Thanks in advance...

ALTER PROCEDURE dbo.RetrieveAllSessionHistoryItemsContaining
(
@Pattern Varchar(255),
@From DateTime,
@To DateTime
)
AS
DECLARE @Query VARCHAR(500)
SET @Query = 'SELECT * FROM SessionHistoryItems, Sessions WHERE
SessionHistoryItems.SessionId = Sessions.SessionId'

DECLARE @conditions nvarchar(257)
SET @conditions = '';

IF LEN(@Pattern) > 0 BEGIN
SET @conditions = @conditions + ' Sessions.Login LIKE ''%' + @Pattern
+ '%'''
END

IF @From IS NOT NULL BEGIN
IF LEN(@conditions) > 0 BEGIN
SET @conditions = @conditions + ' AND '
END
SET @conditions = @conditions + ' SessionHistoryItems.LoggedOutAt >=
' + @From
END

IF @To IS NOT NULL BEGIN
IF LEN(@conditions) > 0 BEGIN
SET @conditions = @conditions + ' AND '
END
SET @conditions = @conditions + ' SessionHistoryItems.LoggedInAt <= '
+ @To
END

IF LEN(@conditions) > 0 BEGIN
EXEC(@Query + ' AND ' + @conditions)
END
ELSE BEGIN
EXEC(@Query)
END
RETURN

[Simon Hayes]

<an***@iilab.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

I'm keeping in the database a log of all the sessions for my
application. I'm trying to write a stored procedure that returns all
the sessions that; the login contains a certain string, loggedin after
a certain datetime and loggedout before another datetime. Any
combination of these parameters can be used and, if none, returns all
the log.

Below is the code I came up with but I'm having a "Syntax error
converting datetime from character string" exception. When not using
DateTime parameters everything works fine. Can you tell me how can I
avoid this exception? Thanks in advance...

ALTER PROCEDURE dbo.RetrieveAllSessionHistoryItemsContaining
(
@Pattern Varchar(255),
@From DateTime,
@To DateTime
)
AS
DECLARE @Query VARCHAR(500)
SET @Query = 'SELECT * FROM SessionHistoryItems, Sessions WHERE
SessionHistoryItems.SessionId = Sessions.SessionId'

DECLARE @conditions nvarchar(257)
SET @conditions = '';

IF LEN(@Pattern) > 0 BEGIN
SET @conditions = @conditions + ' Sessions.Login LIKE ''%' + @Pattern
+ '%'''
END

IF @From IS NOT NULL BEGIN
IF LEN(@conditions) > 0 BEGIN
SET @conditions = @conditions + ' AND '
END
SET @conditions = @conditions + ' SessionHistoryItems.LoggedOutAt >=
' + @From
END

IF @To IS NOT NULL BEGIN
IF LEN(@conditions) > 0 BEGIN
SET @conditions = @conditions + ' AND '
END
SET @conditions = @conditions + ' SessionHistoryItems.LoggedInAt <= '
+ @To
END

IF LEN(@conditions) > 0 BEGIN
EXEC(@Query + ' AND ' + @conditions)
END
ELSE BEGIN
EXEC(@Query)
END
RETURN

It looks like you need to CAST or CONVERT the datetime to a string (and add
quotes) in order to build up the @conditions string:

declare @dt datetime
set @dt = getdate()

select 'x' + @dt -- fails
select 'x''' + cast(@dt as varchar(20)) + '''' -- succeeds

But in this case, using sp_executesql would probably be a better approach
anyway:

exec sp_executesql
N'select col1, col2 from dbo.MyTable where datecol >= @From and datecol <=
@To',
N'@From datetime, @To datetime',
@From, @To

See sp_executesql in Books Online, and also these articles for more
information/ideas:

http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html

Simon