help with dateTime datatype within DSQL

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)

help with dateTime datatype within DSQL

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-09-08 : 01:10:57

I have the following code which I have tested as working outside of dynamic sql, here I do not get an error. I have now added the dynamic sql wrapper and modified the ' so it should work across multiple database and when run it I get the following error. How do I specify or keep the variables as datetime datatype within dsql as I could outside? I was hoping not to have to do any converts. I am probably specifing this part incorrectly: '+@StartDateRange+'

USE [VC]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[TotalDuration_MTS]
@SiteName = N'ben',
@EventType = N'quadalarm',
@StartDateRange = N'1/1/2007',
@EndDateRange = N'1/1/2008',
@DBName = N'vc'

SELECT 'Return Value' = @return_value

GO

Msg 241, Level 16, State 1, Procedure TotalDuration_MTS, Line 14
Conversion failed when converting datetime from character string.

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[TotalDuration_MTS] Script Date: 09/08/2008 10:40:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TotalDuration_MTS]

@SiteName nvarchar(50) = NULL,
@EventType nvarchar(50) = NULL,
@StartDateRange DateTime = NULL,
@EndDateRange DateTime = NULL,
@DBName nvarchar (100) = NULL

AS

--SET NOCOUNT ON
Declare @sql varchar(max)
Select @sql=''
Select @sql=@sql+ 'SELECT Days, Hours, Minutes, Seconds, TSec, '''+name+''' AS DatabaseName, EventCount
FROM (SELECT SUM(TotalSeconds) / 86400 AS Days, SUM(TotalSeconds) % 86400 / 3600 AS Hours, SUM(TotalSeconds) % 3600 / 60 AS Minutes, SUM(TotalSeconds) % 60 AS Seconds, Sum(TotalSeconds) AS TSec, Count(EventID) AS EventCount
FROM (SELECT DateLimit.EventID, DATEDIFF(second, DateLimit.StartTime, DateLimit.EndTime) AS TotalSeconds
FROM (SELECT EventID,
CASE
WHEN EventDetails.EventStartTime < '+@StartDateRange+' THEN '+@StartDateRange+'
WHEN EventDetails.EventStartTime IS NULL THEN '+@StartDateRange+'
ELSE EventDetails.EventStartTime
END AS StartTime,
CASE
WHEN EventDetails.EventEndTime > '+@EndDateRange+' THEN '+@EndDateRange+'
WHEN EventDetails.EventEndTime IS NULL THEN '+@EndDateRange+'
ELSE EventDetails.EventEndTime
END AS EndTime FROM '+name+'.dbo.EventDetails) AS DateLimit INNER JOIN
'+name+'.dbo.EventDetails AS EventDetails_1 ON DateLimit.EventID = EventDetails_1.EventID INNER JOIN
'+name+'.dbo.SiteDetails ON EventDetails_1.SiteID = '+name+'.dbo.SiteDetails.SiteID
WHERE (
(
(NOT((EventDetails_1.EventStartTime < '+@StartDateRange+') AND (EventDetails_1.EventEndTime < '+@StartDateRange+')))
AND
(NOT((EventDetails_1.EventStartTime > '+@EndDateRange+') AND (EventDetails_1.EventEndTime > '+@EndDateRange+')))
AND
(NOT((EventDetails_1.EventStartTime > '+@EndDateRange+') AND (EventDetails_1.EventEndTime IS NULL)))
)
OR
(
((EventDetails_1.EventStartTime < '+@EndDateRange+') AND (EventDetails_1.EventEndTime IS NULL))
)
)
AND SiteName IN (Select Param From fn_MVParam ('''+@SiteName+''','',''))
AND EventType IN (Select Param From fn_MVParam ('''+@EventType+''','','')))
AS DateDif) AS FinalSel
GROUP BY Days, Hours, Minutes, Seconds, TSec, EventCount
' from sys.databases where name='VC' or name like 'VCA%'

Create table #t([Days] int, [Hours] int, [Minutes] int, [Seconds] int, [TSec] int, [DatabaseName] nvarchar(50), [EventCount] int)
Insert into #t

Exec(@sql)
Print @sql

Select [Days], [Hours], [Minutes], [Seconds], [TSec], [DatabaseName], [EventCount]
From #t

Drop Table #t

---SET NOCOUNT OFF

Return

This is the working code outside of dsql.

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[TotalDuration_MTS] Script Date: 09/08/2008 10:40:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TotalDuration_MTS]

@SiteName nvarchar(50) = NULL,
@EventType nvarchar(50) = NULL,
@StartDateRange DateTime = NULL,
@EndDateRange DateTime = NULL,
@DBName nvarchar (100) = NULL

AS

--SET NOCOUNT ON
SELECT Days, Hours, Minutes, Seconds, TSec, EventCount
FROM (SELECT SUM(TotalSeconds) / 86400 AS Days, SUM(TotalSeconds) % 86400 / 3600 AS Hours, SUM(TotalSeconds) % 3600 / 60 AS Minutes, SUM(TotalSeconds) % 60 AS Seconds, Sum(TotalSeconds) AS TSec, Count(EventID) AS EventCount
FROM (SELECT DateLimit.EventID, DATEDIFF(second, DateLimit.StartTime, DateLimit.EndTime) AS TotalSeconds
FROM (SELECT EventID,
CASE
WHEN EventDetails.EventStartTime < @StartDateRange THEN @StartDateRange
WHEN EventDetails.EventStartTime IS NULL THEN @StartDateRange
ELSE EventDetails.EventStartTime
END AS StartTime,
CASE
WHEN EventDetails.EventEndTime > @EndDateRange THEN @EndDateRange
WHEN EventDetails.EventEndTime IS NULL THEN @EndDateRange
ELSE EventDetails.EventEndTime
END AS EndTime FROM EventDetails) AS DateLimit INNER JOIN
EventDetails AS EventDetails_1 ON DateLimit.EventID = EventDetails_1.EventID INNER JOIN
SiteDetails ON EventDetails_1.SiteID = dbo.SiteDetails.SiteID
WHERE (
(
(NOT((EventDetails_1.EventStartTime < @StartDateRange) AND (EventDetails_1.EventEndTime < @StartDateRange)))
AND
(NOT((EventDetails_1.EventStartTime > @EndDateRange) AND (EventDetails_1.EventEndTime > @EndDateRange)))
AND
(NOT((EventDetails_1.EventStartTime > @EndDateRange) AND (EventDetails_1.EventEndTime IS NULL)))
)
OR
(
((EventDetails_1.EventStartTime < @EndDateRange) AND (EventDetails_1.EventEndTime IS NULL))
)
)
AND SiteName IN (Select Param From fn_MVParam (@SiteName,','))
AND EventType IN (Select Param From fn_MVParam (@EventType,',')))
AS DateDif) AS FinalSel
GROUP BY Days, Hours, Minutes, Seconds, TSec, EventCount

---SET NOCOUNT OFF

Return

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-09-08 : 03:30:59

Firstly, rather specify your dates as yyyy/mm/dd, as the format you are using is ambiguous.

Second, you need to wrap the dates in quotes as such:

NOT((EventDetails_1.EventStartTime < ''' + @StartDateRange + '''

--
Gail Shaw
SQL Server MVP

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-09-08 : 03:57:14

Thanks GilaMonster, how do I specify the dates format? Your suggestion sets it as a string right and not DateTime datatype? The datetime picker comes from reporting services as a datetime calendar control. Note in the code I posted without DSQL it works as DateTime datatype? So my question was mainly around how to do this under DSQL?

I have made some changes and it works now however it only seems to work with nvarchar and not DateTime. This works with reporting services with the report parameters set to datetime which surprised me. I thought I would get a conversion error?

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[TotalDuration_MTS] Script Date: 09/08/2008 10:40:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TotalDuration_MTS]

@SiteName nvarchar(50),
@EventType nvarchar(50),
@StartDateRange nvarchar(22),
@EndDateRange nvarchar(22),
@DBName nvarchar (100)

AS

--SET NOCOUNT ON
Declare @sql varchar(max)
Select @sql=''
Select @sql=@sql+ 'SELECT Days, Hours, Minutes, Seconds, TSec, '''+name+''' AS DatabaseName, EventCount
FROM (SELECT SUM(TotalSeconds) / 86400 AS Days, SUM(TotalSeconds) % 86400 / 3600 AS Hours, SUM(TotalSeconds) % 3600 / 60 AS Minutes, SUM(TotalSeconds) % 60 AS Seconds, Sum(TotalSeconds) AS TSec, Count(EventID) AS EventCount
FROM (SELECT DateLimit.EventID, DATEDIFF(second, DateLimit.StartTime, DateLimit.EndTime) AS TotalSeconds
FROM (SELECT EventID,
CASE
WHEN EventDetails.EventStartTime < '''+@StartDateRange+''' THEN '''+@StartDateRange+'''
WHEN EventDetails.EventStartTime IS NULL THEN '''+@StartDateRange+'''
ELSE EventDetails.EventStartTime
END AS StartTime,
CASE
WHEN EventDetails.EventEndTime > '''+@EndDateRange+''' THEN '''+@EndDateRange+'''
WHEN EventDetails.EventEndTime IS NULL THEN '''+@EndDateRange+'''
ELSE EventDetails.EventEndTime
END AS EndTime FROM '+name+'.dbo.EventDetails) AS DateLimit INNER JOIN
'+name+'.dbo.EventDetails AS EventDetails_1 ON DateLimit.EventID = EventDetails_1.EventID INNER JOIN
'+name+'.dbo.SiteDetails ON EventDetails_1.SiteID = '+name+'.dbo.SiteDetails.SiteID
WHERE (
(
(NOT((EventDetails_1.EventStartTime < '''+@StartDateRange+''') AND (EventDetails_1.EventEndTime < '''+@StartDateRange+''')))
AND
(NOT((EventDetails_1.EventStartTime > '''+@EndDateRange+''') AND (EventDetails_1.EventEndTime > '''+@EndDateRange+''')))
AND
(NOT((EventDetails_1.EventStartTime > '''+@EndDateRange+''') AND (EventDetails_1.EventEndTime IS NULL)))
)
OR
(
((EventDetails_1.EventStartTime < '''+@EndDateRange+''') AND (EventDetails_1.EventEndTime IS NULL))
)
)
AND SiteName IN (Select Param From fn_MVParam ('''+@SiteName+''','',''))
AND EventType IN (Select Param From fn_MVParam ('''+@EventType+''','',''))
AND '''+name+''' IN (Select Param From fn_MVParam ('''+@DBName+''','','')))
AS DateDif) AS FinalSel
GROUP BY Days, Hours, Minutes, Seconds, TSec, EventCount
' from sys.databases where name='VC' or name like 'VCA%'

Create table #t([Days] int, [Hours] int, [Minutes] int, [Seconds] int, [TSec] int, [DatabaseName] nvarchar(50), [EventCount] int)
Insert into #t

Exec(@sql)
Print @sql

Select [Days], [Hours], [Minutes], [Seconds], [TSec], [DatabaseName], [EventCount]
From #t

Drop Table #t

---SET NOCOUNT OFF

Return

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-08 : 10:00:31

Your stored procedure should accept properly-typed parameters (i.e., DateTime data types), and you should use sp_executeSQL to execute your dynamic SQL with parameters. then, you never need to worry about date formatting, or delimiting, or what happens if someone calls your stored proc and passes in the word "HELLO" as a "date" parameter.

See: http://msdn.microsoft.com/en-us/library/ms188001.aspx

Also, as always, Google sp_executeSQL for more ideas and examples. Never concatentate random, poorly-typed input to dynamic SQL the way you are, always use sp_ExecuteSQL and use properly-typed parameters.

- Jeff
http://weblogs.sqlteam.com/JeffS

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-09-08 : 21:45:57

Thanks jsmith8858 I have modified and it now works with DateTime formats correctly. I found this very useful and now I am starting to actually understand dynamic sql a little better:

http://www.codeproject.com/KB/database/Building_Dynamic_SQL.aspx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-09 : 03:17:01

Refer this too www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources