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)
[color=#FF0000]SET @sql = 'select top 50 * from view_jydhwxx where 1=1 '[/color]
如何将以上这条语句用下面的这条替换
 "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

create proc sp_test
(
  @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

------解决方案--------------------
楼主的编程很规范，值得称赞，不过有点繁琐，可以使用case when 来实现，比如：
select * from xxx
where case when @a<>'' then 拼接字符串 else '' end 等等，详细可以看我的文章：
http://blog.csdn.net/dba_huangzj/article/details/7684520