create proc [dbo].[BS_JydHwxx_query1]
@sxhh char(10)='',
@fhrxm char(20)='',
@fhrdh char(20)='',
@shrxm char(20)='',
@shrdh char(20)='',
@sdz char(20)='',
@zdz char(20)='',
@hwmc char(20) ='',
@js int,
@zdr char(20) ='',
@jdsj1 smalldatetime,
@jdsj2 smalldatetime,
@jydzt char(10)='',
@jdzz char(10)='',
@fgsbm char(10)=''as
BEGIN
DECLARE @sql AS Varchar(1000)
SET @sql = 'select top 50 * from view_jydhwxx where 1=1 '
如何将以上这条语句用下面的这条替换
"select top " + limit + " * from View_jydhwxx where fgsbm in (" + fgsbm + ") and dzzbm in ("+result+") and jydbh not in(select top " + start + " jydbh from View_jydhwxx where fgsbm in (" + fgsbm + ") and dzzbm in ("+result+") order by jydbh desc) order by jydbh desc";
if @sxhh <>''
begin
set @sql= @sql+N' and sxhh like '+'''%'+rtrim(@sxhh)+'%'''
end
if @fhrxm <>''
begin
set @sql =@sql +N' and fhrxm like '+'''%'+rtrim(@fhrxm)+'%'''
end
if @fhrdh <>''
begin
set @sql =@sql +N' and fhrdh like '+'''%'+rtrim(@fhrdh)+'%'''
end
if @shrxm <>''
begin
set @sql =@sql +N' and shrxm like '+'''%'+rtrim(@shrxm)+'%'''
end
if @shrdh <>''
begin
set @sql =@sql +N' and shrdh like '+'''%'+rtrim(@shrdh)+'%'''
end
if @sdz <>''
begin
set @sql=@sql+N' and sdz like '+'''%'+rtrim(@sdz)+'%'''
end
if @zdz <>''
begin
set @sql=@sql+N' and zdz like '+'''%'+rtrim(@zdz)+'%'''
end
if @hwmc <>''
begin
set @sql=@sql+N' and hwlxmc like '+'''%'+rtrim(@hwmc)+'%'''
end
if @js <> 0
begin
set @sql =@sql +N' and jshj ='+''''+rtrim(@js)+''''
end
if @zdr <>''
begin
set @sql=@sql+N' and zdr like '+'''%'+rtrim(@zdr)+'%'''
end
if @jdsj1 <>'' and @jdsj2 <>''
begin
set @sql=@sql +N' and jhrq >= '+''''+CONVERT(VARCHAR(50),@jdsj1,121)+'''' +N' and jhrq <= '+''''+CONVERT(VARCHAR(50),@jdsj2,121)+''''
end
if @jydzt<>'05'
begin
set @sql=@sql+N' and jydzt = '+''''+rtrim(@jydzt)+''''
end
if @fgsbm<>''
begin
set @sql=@sql+N' and fgsbm in '+'('+rtrim(@fgsbm)+')'--去掉''
end
EXEC(@sql)
end
@sxhh char(10)='',
@fhrxm char(20)='',
@fhrdh char(20)='',
@shrxm char(20)='',
@shrdh char(20)='',
@sdz char(20)='',
@zdz char(20)='',
@hwmc char(20) ='',
@js int,
@zdr char(20) ='',
@jdsj1 smalldatetime,
@jdsj2 smalldatetime,
@jydzt char(10)='',
@jdzz char(10)='',
@fgsbm char(10)=''as
BEGIN
DECLARE @sql AS Varchar(1000)
SET @sql = 'select top 50 * from view_jydhwxx where 1=1 '
如何将以上这条语句用下面的这条替换
"select top " + limit + " * from View_jydhwxx where fgsbm in (" + fgsbm + ") and dzzbm in ("+result+") and jydbh not in(select top " + start + " jydbh from View_jydhwxx where fgsbm in (" + fgsbm + ") and dzzbm in ("+result+") order by jydbh desc) order by jydbh desc";
if @sxhh <>''
begin
set @sql= @sql+N' and sxhh like '+'''%'+rtrim(@sxhh)+'%'''
end
if @fhrxm <>''
begin
set @sql =@sql +N' and fhrxm like '+'''%'+rtrim(@fhrxm)+'%'''
end
if @fhrdh <>''
begin
set @sql =@sql +N' and fhrdh like '+'''%'+rtrim(@fhrdh)+'%'''
end
if @shrxm <>''
begin
set @sql =@sql +N' and shrxm like '+'''%'+rtrim(@shrxm)+'%'''
end
if @shrdh <>''
begin
set @sql =@sql +N' and shrdh like '+'''%'+rtrim(@shrdh)+'%'''
end
if @sdz <>''
begin
set @sql=@sql+N' and sdz like '+'''%'+rtrim(@sdz)+'%'''
end
if @zdz <>''
begin
set @sql=@sql+N' and zdz like '+'''%'+rtrim(@zdz)+'%'''
end
if @hwmc <>''
begin
set @sql=@sql+N' and hwlxmc like '+'''%'+rtrim(@hwmc)+'%'''
end
if @js <> 0
begin
set @sql =@sql +N' and jshj ='+''''+rtrim(@js)+''''
end
if @zdr <>''
begin
set @sql=@sql+N' and zdr like '+'''%'+rtrim(@zdr)+'%'''
end
if @jdsj1 <>'' and @jdsj2 <>''
begin
set @sql=@sql +N' and jhrq >= '+''''+CONVERT(VARCHAR(50),@jdsj1,121)+'''' +N' and jhrq <= '+''''+CONVERT(VARCHAR(50),@jdsj2,121)+''''
end
if @jydzt<>'05'
begin
set @sql=@sql+N' and jydzt = '+''''+rtrim(@jydzt)+''''
end
if @fgsbm<>''
begin
set @sql=@sql+N' and fgsbm in '+'('+rtrim(@fgsbm)+')'--去掉''
end
EXEC(@sql)
end
(
@value1 int = null,
@value2 int = null
)
as
begin
select *
from table
where ((@value1 is null) or (table.Value1 = @value1))
and
((@value2 is null) or (table.Value2 = @value2))
end
select * from xxx
where case when @a<>'' then 拼接字符串 else '' end 等等,详细可以看我的文章:
http://blog.csdn.net/dba_huangzj/article/details/7684520