Dynamic SQL in Stored Procedure - Datetime parameters

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_CAL开发者_如何学运维ENDARParticipants 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

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

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