CREATE PROCEDURE Pr_test3 @edittime varchar(255) AS declare @sql4 varchar(500) set @sql4='select * from (SELECT BC_area.Name as areaname, BC_place.Name AS placename,BC_member.Name AS name, BC_patrolrecord.MemberId, BC_patrolrecord.CheckDateTime, BC_patrolrecord.EditFlag, BC_patrolrecord.typeid FROM BC_patrolrecord LEFT OUTER JOIN BC_area ON BC_area.ID=BC_patrolrecord.areaId LEFT OUTER JOIN BC_place ON BC_patrolrecord.PlaceId = BC_place.ID LEFT OUTER JOIN BC_member ON BC_patrolrecord.MemberId = BC_member.ID WHERE (BC_patrolrecord.MemberId IS NOT NULL) AND (BC_patrolrecord.PlaceId IS NOT NULL)) tab' exec(@sql4) if (@edittiem is not null) begin set @sql4=@sql4+'BC_patrolrecord.EditFlag>convert(varchar(20),'+@edittime+',120)' exec (@sql4) GO 提示错误
CREATE PROCEDURE Pr_test3 @edittime varchar(255) AS declare @sql4 varchar(500) set @sql4='select * from (SELECT BC_area.Name as areaname, BC_place.Name AS placename,BC_member.Name AS name, BC_patrolrecord.MemberId, BC_patrolrecord.CheckDateTime, BC_patrolrecord.EditFlag, BC_patrolrecord.typeid FROM BC_patrolrecord LEFT OUTER JOIN BC_area ON BC_area.ID=BC_patrolrecord.areaId LEFT OUTER JOIN BC_place ON BC_patrolrecord.PlaceId = BC_place.ID LEFT OUTER JOIN BC_member ON BC_patrolrecord.MemberId = BC_member.ID WHERE (BC_patrolrecord.MemberId IS NOT NULL) AND (BC_patrolrecord.PlaceId IS NOT NULL)) tab' exec(@sql4) if (@edittime is not null) set @sql4=@sql4+' where BC_patrolrecord.EditFlag>convert(varchar(20),'+@edittime+',120)'exec (@sql4)@sql4 500再大一点
CREATE PROCEDURE Pr_test3 @edittime varchar(255) AS declare @sql4 varchar(1000)set @sql4='select * from (SELECT BC_area.Name as areaname, BC_place.Name AS placename,BC_member.Name AS name, BC_patrolrecord.MemberId, BC_patrolrecord.CheckDateTime, BC_patrolrecord.EditFlag, BC_patrolrecord.typeid FROM BC_patrolrecord LEFT OUTER JOIN BC_area ON BC_area.ID=BC_patrolrecord.areaId LEFT OUTER JOIN BC_place ON BC_patrolrecord.PlaceId = BC_place.ID LEFT OUTER JOIN BC_member ON BC_patrolrecord.MemberId = BC_member.ID WHERE (BC_patrolrecord.MemberId IS NOT NULL) AND (BC_patrolrecord.PlaceId IS NOT NULL)) tab' exec(@sql4) if (@edittime is not null) begin 去掉set @sql4=@sql4+'BC_patrolrecord.EditFlag>convert(varchar(20),'+@edittime+',120)' exec (@sql4) GO
1、declare @sql4 varchar[color=#FF0000](1000) 2、if (@edittime is not null) 你的是(@edittiem 3、if后面的begin去掉
修改后,如下: CREATE PROCEDURE Pr_test7 @name nvarchar(255), @member nvarchar(255), @dd nvarchar(255), @edittime nvarchar(255), @checktime1 nvarchar(255), @checktime2 nvarchar(255) AS declare @sql varchar(8000) set @sql='select * from (SELECT BC_area.Name as areaname, BC_place.Name AS placename, BC_member.Name AS membername, BC_patrolrecord.MemberId, BC_patrolrecord.CheckDateTime, BC_patrolrecord.EditFlag as editflag, BC_patrolrecord.typeid FROM BC_patrolrecord LEFT OUTER JOIN BC_area ON BC_patrolrecord.AreaId = BC_area.ID LEFT OUTER JOIN BC_place ON BC_patrolrecord.PlaceId = BC_place.ID LEFT OUTER JOIN BC_member ON BC_patrolrecord.MemberId = BC_member.ID WHERE (BC_patrolrecord.MemberId IS NOT NULL) AND (BC_patrolrecord.PlaceId IS NOT NULL)) tab where areaname is not null' if (@name is not null and @name!='') begin set @sql=@sql+ 'and areaname like ''%'+ @name +'%''' end if (@member is not null and @member!='') begin set @sql=@sql+ 'and membername like ''%'+ @member+'%''' end if (@name is not null and @dd!='') begin set @sql=@sql+ 'and placename like ''%'+ @dd +'%''' end if (@edittime is not null and @edittime!='') set @sql=@sql+ ' and editflag > convert(varchar(20),'+@edittime+',120)'exec(@sql) GO 在调试@edittime输入:2008-11-16 18:15:15时提示错误: 服务器: 消息 170,级别 15,状态 1,行 9 [Microsoft][ODBC SQL Server Driver][SQL Server]第 9 行: '18' 附近有语法错误。请高人指点怎么改???
CREATE PROCEDURE Pr_test7 @name nvarchar(255), @member nvarchar(255), @dd nvarchar(255), @edittime nvarchar(255), @checktime1 nvarchar(255), @checktime2 nvarchar(255) AS declare @sql varchar(8000) set @sql='select * from (SELECT BC_area.Name as areaname, BC_place.Name AS placename, BC_member.Name AS membername, BC_patrolrecord.MemberId, BC_patrolrecord.CheckDateTime, BC_patrolrecord.EditFlag as editflag, BC_patrolrecord.typeid FROM BC_patrolrecord LEFT OUTER JOIN BC_area ON BC_patrolrecord.AreaId = BC_area.ID LEFT OUTER JOIN BC_place ON BC_patrolrecord.PlaceId = BC_place.ID LEFT OUTER JOIN BC_member ON BC_patrolrecord.MemberId = BC_member.ID WHERE (BC_patrolrecord.MemberId IS NOT NULL) AND (BC_patrolrecord.PlaceId IS NOT NULL)) tab where areaname is not null' if (@name is not null and @name!='') begin set @sql=@sql+ 'and areaname like ''%'+ @name +'%''' end if (@member is not null and @member!='') begin set @sql=@sql+ 'and membername like ''%'+ @member+'%''' end if (@name is not null and @dd!='') begin set @sql=@sql+ 'and placename like ''%'+ @dd +'%''' end if (@edittime is not null and @edittime!='') set @sql=@sql+ ' and editflag > convert(varchar(20),'''+@edittime+''',120)' exec(@sql) GO
-->
declare @sql4 varchar(8000) 字符串被你的定义截断了。
declare @sql4 varchar(500)
-->
declare @sql4 varchar(max)
@edittime varchar(255)
AS
declare @sql4 varchar(500)
set @sql4='select * from (SELECT BC_area.Name as areaname, BC_place.Name AS placename,BC_member.Name AS name, BC_patrolrecord.MemberId, BC_patrolrecord.CheckDateTime, BC_patrolrecord.EditFlag, BC_patrolrecord.typeid FROM BC_patrolrecord LEFT OUTER JOIN BC_area ON BC_area.ID=BC_patrolrecord.areaId LEFT OUTER JOIN BC_place ON BC_patrolrecord.PlaceId = BC_place.ID LEFT OUTER JOIN BC_member ON BC_patrolrecord.MemberId = BC_member.ID WHERE (BC_patrolrecord.MemberId IS NOT NULL) AND (BC_patrolrecord.PlaceId IS NOT NULL)) tab'
exec(@sql4)
if (@edittiem is not null) begin
set @sql4=@sql4+'BC_patrolrecord.EditFlag>convert(varchar(20),'+@edittime+',120)'
exec (@sql4)
GO
提示错误
@edittime varchar(255)
AS
declare @sql4 varchar(500)
set @sql4='select * from (SELECT BC_area.Name as areaname, BC_place.Name AS placename,BC_member.Name AS name, BC_patrolrecord.MemberId, BC_patrolrecord.CheckDateTime, BC_patrolrecord.EditFlag, BC_patrolrecord.typeid FROM BC_patrolrecord LEFT OUTER JOIN BC_area ON BC_area.ID=BC_patrolrecord.areaId LEFT OUTER JOIN BC_place ON BC_patrolrecord.PlaceId = BC_place.ID LEFT OUTER JOIN BC_member ON BC_patrolrecord.MemberId = BC_member.ID WHERE (BC_patrolrecord.MemberId IS NOT NULL) AND (BC_patrolrecord.PlaceId IS NOT NULL)) tab'
exec(@sql4)
if (@edittime is not null)
set @sql4=@sql4+' where BC_patrolrecord.EditFlag>convert(varchar(20),'+@edittime+',120)'exec (@sql4)@sql4 500再大一点
CREATE PROCEDURE Pr_test3
@edittime varchar(255)
AS
declare @sql4 varchar(1000)set @sql4='select * from (SELECT BC_area.Name as areaname, BC_place.Name AS placename,BC_member.Name AS name, BC_patrolrecord.MemberId, BC_patrolrecord.CheckDateTime, BC_patrolrecord.EditFlag, BC_patrolrecord.typeid FROM BC_patrolrecord LEFT OUTER JOIN BC_area ON BC_area.ID=BC_patrolrecord.areaId LEFT OUTER JOIN BC_place ON BC_patrolrecord.PlaceId = BC_place.ID LEFT OUTER JOIN BC_member ON BC_patrolrecord.MemberId = BC_member.ID WHERE (BC_patrolrecord.MemberId IS NOT NULL) AND (BC_patrolrecord.PlaceId IS NOT NULL)) tab'
exec(@sql4)
if (@edittime is not null) begin 去掉set @sql4=@sql4+'BC_patrolrecord.EditFlag>convert(varchar(20),'+@edittime+',120)'
exec (@sql4)
GO
2、if (@edittime is not null) 你的是(@edittiem
3、if后面的begin去掉
CREATE PROCEDURE Pr_test7
@name nvarchar(255),
@member nvarchar(255),
@dd nvarchar(255),
@edittime nvarchar(255),
@checktime1 nvarchar(255),
@checktime2 nvarchar(255)
AS
declare @sql varchar(8000)
set @sql='select * from (SELECT BC_area.Name as areaname, BC_place.Name AS placename, BC_member.Name AS membername,
BC_patrolrecord.MemberId, BC_patrolrecord.CheckDateTime,
BC_patrolrecord.EditFlag as editflag, BC_patrolrecord.typeid
FROM BC_patrolrecord LEFT OUTER JOIN
BC_area ON BC_patrolrecord.AreaId = BC_area.ID LEFT OUTER JOIN
BC_place ON BC_patrolrecord.PlaceId = BC_place.ID LEFT OUTER JOIN
BC_member ON BC_patrolrecord.MemberId = BC_member.ID
WHERE (BC_patrolrecord.MemberId IS NOT NULL) AND
(BC_patrolrecord.PlaceId IS NOT NULL)) tab where areaname is not null'
if (@name is not null and @name!='') begin
set @sql=@sql+ 'and areaname like ''%'+ @name +'%'''
end
if (@member is not null and @member!='') begin
set @sql=@sql+ 'and membername like ''%'+ @member+'%'''
end
if (@name is not null and @dd!='') begin
set @sql=@sql+ 'and placename like ''%'+ @dd +'%'''
end
if (@edittime is not null and @edittime!='')
set @sql=@sql+ ' and editflag > convert(varchar(20),'+@edittime+',120)'exec(@sql)
GO
在调试@edittime输入:2008-11-16 18:15:15时提示错误:
服务器: 消息 170,级别 15,状态 1,行 9
[Microsoft][ODBC SQL Server Driver][SQL Server]第 9 行: '18' 附近有语法错误。请高人指点怎么改???
@name nvarchar(255),
@member nvarchar(255),
@dd nvarchar(255),
@edittime nvarchar(255),
@checktime1 nvarchar(255),
@checktime2 nvarchar(255)
AS
declare @sql varchar(8000)
set @sql='select * from (SELECT BC_area.Name as areaname, BC_place.Name AS placename, BC_member.Name AS membername,
BC_patrolrecord.MemberId, BC_patrolrecord.CheckDateTime,
BC_patrolrecord.EditFlag as editflag, BC_patrolrecord.typeid
FROM BC_patrolrecord LEFT OUTER JOIN
BC_area ON BC_patrolrecord.AreaId = BC_area.ID LEFT OUTER JOIN
BC_place ON BC_patrolrecord.PlaceId = BC_place.ID LEFT OUTER JOIN
BC_member ON BC_patrolrecord.MemberId = BC_member.ID
WHERE (BC_patrolrecord.MemberId IS NOT NULL) AND
(BC_patrolrecord.PlaceId IS NOT NULL)) tab where areaname is not null'
if (@name is not null and @name!='') begin
set @sql=@sql+ 'and areaname like ''%'+ @name +'%'''
end
if (@member is not null and @member!='') begin
set @sql=@sql+ 'and membername like ''%'+ @member+'%'''
end
if (@name is not null and @dd!='') begin
set @sql=@sql+ 'and placename like ''%'+ @dd +'%'''
end
if (@edittime is not null and @edittime!='')
set @sql=@sql+ ' and editflag > convert(varchar(20),'''+@edittime+''',120)' exec(@sql)
GO