DECLARE @WHERES VARCHAR(1000)
DECLARE @a datetime
DECLARE @b datetime
DECLARE @c varchar(100)
set @a=N'2012-08-14 15:00:00'
set @b=N'2012-08-14 19:30:00'
set @c='CAA396ED-9DE2-4C80-B4A8-0BFBF3DBBC33'
--SET @WHERES='AND [email protected]+' AND GpsTime>[email protected]+''' AND GpsTime<[email protected]+''''
if @@ERROR<>0
begin
SET @WHERES=''
end
问题一块看就明白，环境是sql2008，这蛋疼的引号让人很蛋疼。希望有同行可以拼接成功，并运行成功。
------解决方案--------------------
- SQL code
DECLARE @WHERES VARCHAR(1000)DECLARE @a datetimeDECLARE @b datetimeDECLARE @c varchar(100)set @a=N'2012-08-14 15:00:00'set @b=N'2012-08-14 19:30:00'set @c='CAA396ED-9DE2-4C80-B4A8-0BFBF3DBBC33'SET @WHERES='AND [email protected]+' AND GpsTime>='+quotename(@a,'''')+' AND GpsTime<='+QUOTENAME(@b,'''')print @WHERES--AND CompanyId=CAA396ED-9DE2-4C80-B4A8-0BFBF3DBBC33 AND GpsTime>='08 14 2012 3:00PM' AND GpsTime<='08 14 2012 7:30PM'if @@ERROR<>0begin SET @WHERES=''end--直接用quotename就行了
------解决方案--------------------
DATETIME需要转换，GUID需要引号
- SQL code
DECLARE @WHERES VARCHAR(1000)DECLARE @a datetimeDECLARE @b datetimeDECLARE @c varchar(100)set @a=N'2012-08-14 15:00:00'set @b=N'2012-08-14 19:30:00'set @c='CAA396ED-9DE2-4C80-B4A8-0BFBF3DBBC33'SET @WHERES='AND [email protected]+''' AND GpsTime>='''+CONVERT(VARCHAR(19),@a,120)+''' AND GpsTime<='''+CONVERT(VARCHAR(19),@b,120)+''''if @@ERROR<>0begin SET @WHERES=''end
------解决方案--------------------
- SQL code
DECLARE @WHERES VARCHAR(1000)DECLARE @a datetimeDECLARE @b datetimeDECLARE @c varchar(100)set @a=N'2012-08-14 15:00:00'set @b=N'2012-08-14 19:30:00'set @c='CAA396ED-9DE2-4C80-B4A8-0BFBF3DBBC33'SET @WHERES='AND [email protected]+''' AND GpsTime>='''+CONVERT(VARCHAR(19),@a,120)+''' AND GpsTime<='''+CONVERT(VARCHAR(19),@b,120)+''''if @@ERROR<>0begin SET @WHERES=''end