存储过程？

Question

看看有问题？CREATE PROCEDURE read_gjjl 
                                      (
                                          @ypmc varchar(30)='',
                                          @cd varchar(30)='',
                                          @dh_Fdate varchar(10)='',
                                          @dh_Tdate varchar(10)='', 
                                          @ys_FDate varchar(10)='',
                                          @ys_TDate Varchar(10)='',
                                          @dh_b varchar(2)='0',
                                          @ys_b varchar(2)='0'
                                      ) 
AS
BEGIN
SELECT ysjl.ypbh, ypsz.ypjx, ypsz.ypmc, ypsz.tymc, ypsz.gg, ypsz.dw, ypsz.cd, 
      ypsz.pzwh, ypsz.baoz, ysjl.djh, ysjl.ysph, ysjl.ysyxq, ysjl.yskq, ysjl.yssl, ysjl.zcsb, 
      ysjl.hgz, ysjl.wgzk, ysjl.ysjl, ysjl.cctj, ysjl.ysy, ysjl.bgy,ysjl.dhrq,ysjl.ysrq
FROM ysjl 
INNER JOIN  ypsz ON ysjl.ypbh = ypsz.ypbh
WHERE (ypsz.ypmc like '%'+@ypmc+'%' or ypsz.ypdm like '%'+@ypmc+'%')  
               and (ypsz.cd like '%'+@cd+'%') or (ypsz.cddm like '%'+@cd+'%')
and 
( @dh_b = '1' and (ysjl.dhrq between isnull(@dh_FDate,convert(varchar(10),GetDate(),120)) 
                        and isnull(@dh_TDate,convert(varchar(10),GetDate(),120) ))
or
  @ys_b = '1' and (ysjl.ysrq between isnull(@ys_Fdate,convert(varchar(10),GetDate(),120)) 
               and isnull(varchar(10),@ys_TDate,120))
)END
GO

Answer 1

看看有问题？CREATE PROCEDURE read_gjjl 
                                      (
                                          @ypmc varchar(30)='',
                                          @cd varchar(30)='',
                                          @dh_Fdate varchar(10)='',
                                          @dh_Tdate varchar(10)='', 
                                          @ys_FDate varchar(10)='',
                                          @ys_TDate Varchar(10)='',
                                          @dh_b varchar(2)='0',
                                          @ys_b varchar(2)='0'
                                      ) 
AS
BEGIN
SELECT ysjl.ypbh, ypsz.ypjx, ypsz.ypmc, ypsz.tymc, ypsz.gg, ypsz.dw, ypsz.cd, 
      ypsz.pzwh, ypsz.baoz, ysjl.djh, ysjl.ysph, ysjl.ysyxq, ysjl.yskq, ysjl.yssl, ysjl.zcsb, 
      ysjl.hgz, ysjl.wgzk, ysjl.ysjl, ysjl.cctj, ysjl.ysy, ysjl.bgy,ysjl.dhrq,ysjl.ysrq
FROM ysjl 
INNER JOIN  ypsz ON ysjl.ypbh = ypsz.ypbh
WHERE (ypsz.ypmc like '%'+@ypmc+'%' or ypsz.ypdm like '%'+@ypmc+'%')  
               and (ypsz.cd like '%'+@cd+'%') or (ypsz.cddm like '%'+@cd+'%')
and 
( (@dh_b = '1' and (ysjl.dhrq between isnull(@dh_FDate,convert(varchar(10),GetDate(),120)) 
                        and isnull(@dh_TDate,convert(varchar(10),GetDate(),120) ))
   )
or
  (@ys_b = '1' and (ysjl.ysrq between isnull(@ys_Fdate,convert(varchar(10),GetDate(),120)) 
               and isnull(varchar(10),@ys_TDate,120))
  )
)END
GO

Answer 2

这样效率如何吗？CREATE PROCEDURE read_gjjl 
                                      (
                                          @ypmc varchar(30)='',
                                          @cd varchar(30)='',
                                          @dh_Fdate varchar(10)='',
                                          @dh_Tdate varchar(10)='', 
                                          @ys_FDate varchar(10)='',
                                          @ys_TDate Varchar(10)='',
                                          @dh_b varchar(2)='0',
                                          @ys_b varchar(2)='0'
                                      ) 
AS
BEGIN
         SELECT *
         into #t
         FROM ysjl 
         INNER JOIN  ypsz ON ysjl.ypbh = ypsz.ypbh   if @dh_b='1'
    begin
     Select * from #t 
     WHERE (ypsz.ypmc like '%'+@ypmc+'%' or ypsz.ypdm like '%'+@ypmc+'%')  
     and (ypsz.cd like '%'+@cd+'%') or (ypsz.cddm like '%'+@cd+'%') 
     and (ysjl.dhrq between isnull(@dh_FDate,convert(varchar(10),GetDate(),120)) 
     and isnull(@dh_TDate,convert(varchar(10),GetDate(),120) )) 
    end
  if @ys_b='1'
    begin 
     Select * from #t 
     WHERE (ypsz.ypmc like '%'+@ypmc+'%' or ypsz.ypdm like '%'+@ypmc+'%')  
     and (ypsz.cd like '%'+@cd+'%') or (ypsz.cddm like '%'+@cd+'%') 
     and (ysjl.dhrq between isnull(@dh_FDate,convert(varchar(10),GetDate(),120)) 
     and isnull(@dh_TDate,convert(varchar(10),GetDate(),120) )) 
    end
    if (@ys_b='1') and  (@dh_b='1') 
   begin
      select * from #t 
      where  (ypsz.ypmc like '%'+@ypmc+'%' or ypsz.ypdm like '%'+@ypmc+'%')  
      and (ypsz.cd like '%'+@cd+'%') or (ypsz.cddm like '%'+@cd+'%')
     and (ysjl.dhrq between isnull(@dh_FDate,convert(varchar(10),GetDate(),120)) 
     and isnull(@dh_TDate,convert(varchar(10),GetDate(),120) )) 
     and (ysjl.dhrq between isnull(@dh_FDate,convert(varchar(10),GetDate(),120)) 
     and isnull(@dh_TDate,convert(varchar(10),GetDate(),120) )) 
   end
   if (@ys_b<>'1') and  (@dh_b<>'1') 
    begin
     select * from #t 
     where  (ypsz.ypmc like '%'+@ypmc+'%' or ypsz.ypdm like '%'+@ypmc+'%')  
           and (ypsz.cd like '%'+@cd+'%') or (ypsz.cddm like '%'+@cd+'%')
    end       drop Table #t
END
GO

Answer 3

CREATE PROCEDURE read_gjjl 
                                      (
                                          @ypmc varchar(30)='',
                                          @cd varchar(30)='',
                                          @dh_Fdate varchar(10)='',
                                          @dh_Tdate varchar(10)='', 
                                          @ys_FDate varchar(10)='',
                                          @ys_TDate Varchar(10)='',
                                          @dh_b varchar(2)='0',
                                          @ys_b varchar(2)='0'
                                      ) 
AS
BEGIN
         SELECT *
         into #t
         FROM ysjl 
         INNER JOIN  ypsz ON ysjl.ypbh = ypsz.ypbhif @ys_b='1'-----select*..
if @dh_b='1'------select*..
if (@ys_b<>'1') and  (@dh_b<>'1') ------select *.....
if (@ys_b='1') and  (@dh_b='1')  ---------seelct * ........这样效率如何？