分页存储过程

  精确查询：

        CREATE PROCEDURE FindCosInfo
(
    @CosID varchar (10),         //课程编号
    @CosName  varchar (20),   //课程名称
    @CosCredit  int                     //课程学分
)
AS
declare @sql varchar (1000)
begin
  set @sql='select * from tb_Course '
  if  @CosID!=''
    begin
      set @sql=@sql+' where CosID= '+cast( @CosID as varchar)
    end
  if (@CosName!= '' and @CosID!='')
    begin
      set @sql=@sql+'and'+'  CosName=  '+cast (@CosName as varchar )
    end
  else
    if (@CosName!='' and @CosID ='')
      begin
        set @sql=@sql+'where '+'  CosName=  '+cast (@CosName as varchar )
      end
  if @CosCredit!= '' and (@CosID!='' or @CosName!='')
    begin
      set @sql=@sql+' and  CosCredit=  '+cast (@CosCredit as varchar )
    end
  else
    if @CosCredit!='' and @CosID=''and @CosName=''
      set @sql=@sql+'where CosCredit= '+cast (@CosCredit as varchar )
  exec (@sql)
end
GO





    下面是模糊查询代码：

           CREATE PROCEDURE FindCosDarkInfo
(
    @CosID varchar (10),
    @CosName  varchar (20),
    @CosCredit  int
)
AS
declare @sql varchar (1000)
begin
  set @sql='select * from tb_Course '
  if  @CosID!=''
    begin
      set @sql=@sql+' where CosID like'+''''+'%'+cast( @CosID as varchar)+'%'+''''  //注意后面的这四个单引

                                                                                                                                    //号是用来代表一个'
    end
  if (@CosName!= '' and @CosID!='')
    begin
      set @sql=@sql+'and'+'  CosName like  '+''''+'%'+cast (@CosName as varchar )+'%'+''''
    end
  else
    if (@CosName!='' and @CosID ='')
      begin
        set @sql=@sql+'where '+'  CosName like  '+''''+'%'+cast (@CosName as varchar )+'%'+''''
      end
  if @CosCredit!= '' and (@CosID!='' or @CosName!='')
    begin
      set @sql=@sql+' and  CosCredit like  '+''''+'%'+cast (@CosCredit as varchar )+'%'+''''
    end
  else
    if @CosCredit!='' and @CosID=''and @CosName=''
      set @sql=@sql+'where CosCredit like '+''''+'%'+cast (@CosCredit as varchar )+'%'+''''
  exec (@sql)
end

GO