我的表结构是这样的:
SQL code
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[attend]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[attend]
GO
CREATE TABLE [dbo].[attend] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[startdate] [datetime] NOT NULL ,
[enddate] [datetime] NOT NULL ,
[eid] [int] NOT NULL ,
[late] [int] NULL ,
[brea] [int] NULL ,
[leave] [int] NULL ,
[neg] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[attend] WITH NOCHECK ADD
CONSTRAINT [PK_attend] PRIMARY KEYCLUSTERED
(
[id]
)ON [PRIMARY]
GO
我要根据输入的条件来查询信息,
有3中情况:
1 只输入eid;
2只输入startdate,和enddate;3eid,startdate,enddate都输入。
我的存储过程如下:
SQL code
IF EXISTS( SELECT * FROM sysobjects WHERE name='attendances_select_countByState_proc' )
DROP PROCEDURE attendances_select_countByState_proc
GO
CREATE PROCEDURE attendances_select_countByState_proc
@eid nvarchar(20),
@start datetime,
@end datetime
AS
DECLARE @sql nvarchar(1000)
SET @sql='SELECT * FROM attend WHERE startdate>='+@start+' AND enddate0)
BEGIN
SET @sql=@sql+' AND eid='+@eid
END
END
EXEC(@sql)
GO
但是有错误,“服务器: 消息 241,级别 16,状态 1,过程 attendances_select_countByState_proc,行 17
从字符串转换为 datetime 时发生语法错误。”
怎么解决啊?
|