Dynamic SQL in Stored Procedure - Datetime parameters

Question

Got a Stored Procedure that has is being converted to Dynamic SQL, the reason is because additional SQL will be passed into the procedure from an external system, before it is executed.

Conversion failed when converting datetime from character string. Here is the full Stored Procedure:

USE [DBName];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER PROCEDURE [DB_Admin].[GetMiniCalendarDataNew]
@userID int, @startDate datetime, @endDate datetime, @JVID int = 0

WITH EXEC AS CALLER
AS

set nocount on

declare @SQLQuery AS NVARCHAR(max)

declare @t as table([day] int, [end] datetime, sortorder int, jv int)

SET @SQLQuery= 'insert into @t([day], [end], sortorder, jv)
select day((A.STARTTIME)) [day], max(a.endtime) ''end'', 3 sortorder,min(a.jv) jv 
from DB_Admin.CSTM_CALENDAR a
join DB_Admin.CSTM_CALENDAR b on a.id<>b.id
join DB_Admin.CSTM_CALENDARParticipants m1 on a.id=m1.CalendarID
join DB_Admin.CSTM_CALENDARParticipants m2 on b.id=m2.CalendarID
join DB_Admin.DTree DTree on a.FolderDataID=DTree.DataID
where a.starttime between ' + CAST(@startDate AS DATETIME) + ' AND ' + CAST(@endDate AS DATETIME) +
' AND DTree.OwnerID > 0
and b.starttime between ' + CAST(@startDate AS DATETIME) + ' AND ' + CAST(@endDate AS DATETIME) +
' AND a.starttime<b.endtime --find overlapping meetings
AND a.endtime>b.starttime --find overlapping meetings
AND M1.PARTICIPANT IN (
select id from DB_Admin.kuaf where id in (
    select id from DB_Admin.kuafchildren
    where childid=' +@userID+')
    or id=' +@userID+
')
AND M2.PARTICIPANT IN (
select id from DB_Admin.kuaf where id in (
    select id from DB_Admin.kuafchildren
    where childid='+@userID+') 
    or id='+@userID+
')'+

--Filter on JV
' AND ( exists (select 1 where a.jv='+@JVID+')
or    '+@JVID+'=0'+
')'+

'group by day(A.STARTTIME)'

+' insert into @t ([day], [end], sortorder, jv)
select day(A.STARTTIME) [day], max(a.endtime) ''end'', 2 SORTORDER,min(a.jv) jv
from DB_Admin.CSTM_CALENDAR a
join DB_Admin.CSTM_CALENDAR b on a.id<>b.id
join DB_Admin.CSTM_CALENDARParticipants m1 on a.id=m1.CalendarID
join DB_Admin.CSTM_CALENDARParticipants m2 on b.id=m2.CalendarID
join DB_Admin.DTree DTree on a.FolderDataID=DTree.DataID
where a.starttime between ' + CAST(@startDate AS DATETIME) +' AND ' +CAST(@endDate AS DATETIME)+
' AND DTree.OwnerID > 0
--Filter on JV
AND ( exists (select 1 where a.jv='+@JVID+')
or  '+@JVID+'=0'+')
and M1.PARTICIPANT IN (
select id from DB_Admin.kuaf where id in (
    select id from DB_Admin.kuafchildren
    where childid='+@userID+') 
    or id='+@userID+
')
group by (A.STARTTIME)'+

' insert into @t ([day], [end], sortorder, jv)
select day(A.STARTTIME) [day], max(a.endtime) ''end'', 1 SORTORDER,min(a.jv) jv
from DB_Admin.CSTM_CALENDAR a
join DB_Admin.CSTM_CALENDARParticipants m1 on a.ID=m1.CalendarID
join DB_Admin.DTree DTree on a.FolderDataID=DTree.DataID
where a.starttime between '+CAST(@startDate AS DATETIME)+' AND '+CAST(@endDate AS DATETIME)+
' AND DTree.OwnerID > 0
--Filter on JV
AND ( exists (select 1 where a.jv='+@JVID+')
or    '+@JVID+'=0'+ ')
and M1.PARTICIPANT NOT IN (
select id from DB_Admin.kuaf where id in (
    select id from DB_Admin.kuafchildren
    where childid='+@userID+') 
    or id='+@userID+'
)
group by (A.STARTTIME)'


--format query
+' select [day], max(month('+CAST(@startDate AS DATETIME)+' [month], max(year('+CAST(@endDate AS DATETIME)+')) [year], max([end]) ''end'', 
    case 
        when max(sortorder)=3 then ''Overlapping'' 
        when max(sortorder)=2 then ''Participating''
        when max(sortorder)=1 then ''Existing''
        when max(sortorder)=0 then ''Empty''

    end sortOrder , min(jv) JVID
from @t
group by [day]
order by sortorder desc'

--EXEC (@SQLQuery)

PRINT (@SQLQuery)

GO

Sorry, this just makes my eyes bleed looking at it. If you want people to help, you should isolate the bit that's causing the error, and format it so that it's a lot easier to read. — ChrisA
– ChrisA, Commented Dec 29, 2010 at 12:17
You need to convert dates to strings before you can concatenate them. — Oded
– Oded, Commented Dec 29, 2010 at 12:19

gbn · Accepted Answer · 2011-01-03 13:44:06Z

2

Well, you need to

Quoting them
Ensure they are string
Make them language/locale safe

So:

...
where a.starttime between ''' + CONVERT(varchar(30), @startDate, 126) +''' AND ''' + ...
...

Edit:

int error. You need to CAST @userID to varchar concatenate it. SQL doesn't do VBA style implicit CASTs

edited Jan 3, 2011 at 13:44

answered Dec 29, 2010 at 12:20

gbn

435k85 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Frode Over a year ago

Thanks, i'm now trying with this convert suggestion. My result is now failing on convert to varchar. "Conversion failed when converting the varchar value 'insert into @t ([day], [end], sortorder, jv) select day((A.STARTTIME)) [day], max(a.endtime) 'end', 3 sortorder,min(a.jv) jv....."

gbn Over a year ago

Try 121 as a format which is ODBC rather then ISO. Also PRINT the string before executing

Frode Over a year ago

The date conversion seems to be resolved. Now getting a issue with int conversion that's failing: Conversion failed when converting the varchar value 'declare @t as table([day] int, [end] datetime....." to data type int. Replaced the datetime parameters temporary with getdate to get it past the conversion errors.

RB. · Accepted Answer · 2010-12-29 12:19:55Z

0

I think your problem is here:

'where a.starttime between ' + CAST(@startDate AS DATETIME) + ' AND ' + CAST(@endDate AS DATETIME) +

You should be converting it to a string, rather than a datetime. See http://msdn.microsoft.com/en-us/library/ms187928.aspx

answered Dec 29, 2010 at 12:19

RB.

37.4k13 gold badges103 silver badges137 bronze badges

Collectives™ on Stack Overflow

Dynamic SQL in Stored Procedure - Datetime parameters

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related