1
CREATE DATABASE TestDB
2GO
3USE TestDB
4GO
5CREATE TABLE tb_Record
6(
7 tRecordTime Datetime
8)
9GO
10INSERT INTO tb_Record
11select DATEADD(yyyy,1,GETDATE())
12Union all
13select DATEADD(dd,1,GETDATE())
14Union all
15select DATEADD(dd,2,GETDATE())
16Union all
17select DATEADD(dd,3,GETDATE())
18Union all
19select DATEADD(dd,4,GETDATE())
20GO
21SELECT * FROM TestDB.dbo.tb_Record
22GO
23IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_SearchRecord]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
24DROP PROC [dbo].[sp_SearchRecord]
25GO
26CREATE PROC dbo.sp_SearchRecord
27 @bgDate DateTime,--开始时间
28 @edDate DateTime--结束时间
29AS
30
31BEGIN
32 DECLARE @ssql VARCHAR(1000)
33 SET @ssql='select a.tRecordTime as ''采集点'' from tb_Record a where 1=1 '
34 IF(@bgDate IS NOT NULL AND CONVERT(VARCHAR(1000),@bgDate)!='')
35 BEGIN
36 SET @ssql=@ssql+ 'AND DATEDIFF(ss,'''+CONVERT(VARCHAR(1000),@bgDate,120)+''',a.tRecordTime)> =0 '
37 END
38 IF(@edDate IS NOT NULL AND CONVERT(VARCHAR(1000),@edDate)!='')
39 BEGIN
40
41 SET @ssql=@ssql+' AND DATEDIFF(ss,a.tRecordTime,'''+CONVERT(VARCHAR(1000),@edDate,120)+''')>=0 '
42 END
43 PRINT @ssql
44 EXEC(@ssql)
45END
46
47GO
48
49
50DECLARE @RC int
51
52
53EXECUTE @RC = [TestDB].[dbo].[sp_SearchRecord]
54 @bgDate='2008-03-10'
55 ,@edDate='2009-03-10'
56
CREATE DATABASE TestDB2
GO3
USE TestDB4
GO5
CREATE TABLE tb_Record 6
(7
tRecordTime Datetime8
)9
GO10
INSERT INTO tb_Record11
select DATEADD(yyyy,1,GETDATE())12
Union all13
select DATEADD(dd,1,GETDATE())14
Union all15
select DATEADD(dd,2,GETDATE())16
Union all17
select DATEADD(dd,3,GETDATE())18
Union all19
select DATEADD(dd,4,GETDATE())20
GO21
SELECT * FROM TestDB.dbo.tb_Record22
GO23
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_SearchRecord]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)24
DROP PROC [dbo].[sp_SearchRecord]25
GO26
CREATE PROC dbo.sp_SearchRecord27
@bgDate DateTime,--开始时间28
@edDate DateTime--结束时间29
AS30
31
BEGIN32
DECLARE @ssql VARCHAR(1000)33
SET @ssql='select a.tRecordTime as ''采集点'' from tb_Record a where 1=1 '34
IF(@bgDate IS NOT NULL AND CONVERT(VARCHAR(1000),@bgDate)!='')35
BEGIN36
SET @ssql=@ssql+ 'AND DATEDIFF(ss,'''+CONVERT(VARCHAR(1000),@bgDate,120)+''',a.tRecordTime)> =0 '37
END38
IF(@edDate IS NOT NULL AND CONVERT(VARCHAR(1000),@edDate)!='')39
BEGIN40
41
SET @ssql=@ssql+' AND DATEDIFF(ss,a.tRecordTime,'''+CONVERT(VARCHAR(1000),@edDate,120)+''')>=0 '42
END43
PRINT @ssql44
EXEC(@ssql)45
END46
47
GO 48
49
50
DECLARE @RC int51
52
53
EXECUTE @RC = [TestDB].[dbo].[sp_SearchRecord] 54
@bgDate='2008-03-10'55
,@edDate='2009-03-10'56