各位朋友,小弟的存储过程如下,但调用的时候:出现如下的错误提示,请问该怎样解决,请各位朋友赐教,谢谢!!!
exec test @begin_date='2007-9-1', @end_date='2007-9-3'错误提示:
消息 241,级别 16,状态 1,过程 test,第 6 行
从字符串转换为 datetime 时发生语法错误。
ALTER procedure [dbo].[test]
@begin_date datetime,
@end_date datetime
as
declare @str varchar(2000)
set @str='select isnull(employee.emp_id,sign_time.emp_id) emp_id,
emp_name, isnull(employee.card_id,sign_time.card_id) card_id,
hire_date, no_sign,sex, principal, depart_name, clock_id,
convert(varchar(16),sign_time,120) sign_time,
from (select e.emp_id,e.emp_name,e.card_id,
hire_date,
Case no_sign WHEN 0 THEN ''要打卡'' ELSE ''免卡'' END no_sign,
Case sex WHEN 0 THEN ''男'' ELSE ''女'' END sex,
d.principal,
d.depart_name from employee e left outer join depart d
on
e.depart_id=d.depart_id) employee,
(select clock_id, card_id, emp_id, sign_time from tmpTRecords where sign_time>='+@begin_date+' and sign_time<='+@end_date+')sign_timewhere
employee.emp_id=sign_time.emp_id'exec (@str)
exec test @begin_date='2007-9-1', @end_date='2007-9-3'错误提示:
消息 241,级别 16,状态 1,过程 test,第 6 行
从字符串转换为 datetime 时发生语法错误。
ALTER procedure [dbo].[test]
@begin_date datetime,
@end_date datetime
as
declare @str varchar(2000)
set @str='select isnull(employee.emp_id,sign_time.emp_id) emp_id,
emp_name, isnull(employee.card_id,sign_time.card_id) card_id,
hire_date, no_sign,sex, principal, depart_name, clock_id,
convert(varchar(16),sign_time,120) sign_time,
from (select e.emp_id,e.emp_name,e.card_id,
hire_date,
Case no_sign WHEN 0 THEN ''要打卡'' ELSE ''免卡'' END no_sign,
Case sex WHEN 0 THEN ''男'' ELSE ''女'' END sex,
d.principal,
d.depart_name from employee e left outer join depart d
on
e.depart_id=d.depart_id) employee,
(select clock_id, card_id, emp_id, sign_time from tmpTRecords where sign_time>='+@begin_date+' and sign_time<='+@end_date+')sign_timewhere
employee.emp_id=sign_time.emp_id'exec (@str)
@begin_date datetime,
@end_date datetime
as
declare @str varchar(2000)
set @str='select isnull(employee.emp_id,sign_time.emp_id) emp_id,
emp_name, isnull(employee.card_id,sign_time.card_id) card_id,
hire_date, no_sign,sex, principal, depart_name, clock_id,
convert(varchar(16),sign_time,120) sign_time,
from(select e.emp_id,e.emp_name,e.card_id,
hire_date,
Case no_sign WHEN 0 THEN ''要打卡'' ELSE ''免卡'' END no_sign,
Case sex WHEN 0 THEN ''男'' ELSE ''女'' END sex,
d.principal,
d.depart_name from employee eleft outer joindepart d
on
e.depart_id=d.depart_id) employee,
(select clock_id, card_id, emp_id, sign_time from tmpTRecords where sign_time>='+convert(varchar(23),@begin_date,121)+' and sign_time<='+convert(varchar(23),@end_date,121)+')sign_timewhere
employee.emp_id=sign_time.emp_id'exec (@str)
--或者直接将这两个参数改为varchar(25)。但建议不要这么做,这样要自己判断日期的有效性。
@begin_date varchar(25),
@end_date varchar(25)
/*
@begin_date datetime,
@end_date datetime
*/
as
declare @str varchar(2000)
set @str='select isnull(employee.emp_id,sign_time.emp_id) emp_id,
emp_name, isnull(employee.card_id,sign_time.card_id) card_id,
hire_date, no_sign,sex, principal, depart_name, clock_id,
convert(varchar(16),sign_time,120) sign_time,
from(select e.emp_id,e.emp_name,e.card_id,
hire_date,
Case no_sign WHEN 0 THEN ''要打卡'' ELSE ''免卡'' END no_sign,
Case sex WHEN 0 THEN ''男'' ELSE ''女'' END sex,
d.principal,
d.depart_name from employee eleft outer joindepart d
on
e.depart_id=d.depart_id) employee,
(select clock_id, card_id, emp_id, sign_time from tmpTRecords where sign_time>='+@begin_date+' and sign_time<='+@end_date+')sign_timewhere
employee.emp_id=sign_time.emp_id'exec (@str)
@begin_date varchar(50),
@end_date varchar(50)
as
declare @str varchar(2000)
set @str='
select * from tmpTRecords where sign_time>='+@begin_date+' and ign_time<='+@end_date
exec (@str)
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOALTER procedure test2
@begin_date varchar(50),
@end_date varchar(50)
as
declare @str varchar(2000)
set @str='
select * from tmpTRecords where sign_time>='+''''+@begin_date+''''+' and sign_time<='+''''+@end_date+''''
exec (@str)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
一般来说在存储过程里写最好还是写varchar类型好点!像数据库里的money,datetime....
我一直就用varchar呵呵!