分页存储过程

精确查询：

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