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<antao@.iilab.com> wrote in message
news:1117467828.906603.299460@.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
