我想在存储过程里拼接一个sql语句,如果一个参数不为空就拼入这个sql语句,最后去掉最后一个and,并执行这个sql语句。哎,总是调不通,请高手帮忙呀CREATE PROCEDURE WHTEL_search_AllTel
(
@jxy nvarchar(50),
@kssj smalldatetime,
@jssj smalldatetime,
@ldhm nvarchar(50),
@ldr nvarchar(50)
)
as
declare @Sql nvarchar(4000)
set @Sql='select * from tel_info where 'if(@jxy<>'')
begin
set @Sql=@Sql+'jxy like ''%'''+@jxy+'''%'' and '
endif(@ldhm<>'')
begin
set @Sql=@Sql+'ldhm like ''%'''+@ldhm+'''%'' and '
end.....exec @Sql
GO
(
@jxy nvarchar(50),
@kssj smalldatetime,
@jssj smalldatetime,
@ldhm nvarchar(50),
@ldr nvarchar(50)
)
as
declare @Sql nvarchar(4000)
set @Sql='select * from tel_info where 'if(@jxy<>'')
begin
set @Sql=@Sql+'jxy like ''%'''+@jxy+'''%'' and '
endif(@ldhm<>'')
begin
set @Sql=@Sql+'ldhm like ''%'''+@ldhm+'''%'' and '
end.....exec @Sql
GO
CREATE PROCEDURE WHTEL_search_AllTel
(
@jxy nvarchar(50),
@kssj smalldatetime,
@jssj smalldatetime,
@ldhm nvarchar(50),
@ldr nvarchar(50)
)
as
declare @Sql nvarchar(4000)
set @Sql='select * from tel_info where 1=1'if(@jxy<>'')
begin
set @Sql=@Sql+'and jxy like ''%'''+@jxy+'''%'' '
endif(@ldhm<>'')
begin
set @Sql=@Sql+' and ldhm like ''%'''+@ldhm+'''%'''
end.....exec @Sql
GO
(
@jxy nvarchar(50),
@kssj smalldatetime,
@jssj smalldatetime,
@ldhm nvarchar(50),
@ldr nvarchar(50)
)
as
declare @Sql nvarchar(4000)
set @Sql='select * from tel_info where 1=1 'if(@jxy<>'')
begin
set @Sql=@Sql+' and jxy like ''%'+@jxy+'%'''
endif(@ldhm<>'')
begin
set @Sql=@Sql+' and ldhm like ''%'+@ldhm+'%'''
end.....exec @Sql
CREATE PROCEDURE WHTEL_search_AllTel
(
@jxy nvarchar(50),
@kssj smalldatetime,
@jssj smalldatetime,
@ldhm nvarchar(50),
@ldr nvarchar(50)
)
as
declare @Sql nvarchar(4000)
set @Sql='select * from tel_info where 1=1 'if(@jxy<>'')
begin
set @Sql=@Sql+' and jxy like ''%'+@jxy+'%'''
endif(@ldhm<>'')
begin
set @Sql=@Sql+' and ldhm like ''%'+@ldhm+'%'''
endprint @Sql
go
exec WHTEL_search_AllTel 'aaaaa','2006-8-9','2006-9-9','bbbbb','ccccc'
subsj between '+@kssj+' and '+@jssj+'