SQL4

本文主要是介绍SQL4，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

create proc proc_exec
@str varchar(200)
as
exec(@str);
go

exec proc_exec 'select * from tb_courses';

drop proc proc_getCount;
--创建一个存储过程，通过表名获取当前表中记录数
create proc proc_getCount
@tableName varchar(50),
@count int output
as
declare @sql varchar(200);
set @sql = 'select @count=count(*) from '+@tableName;
exec(@sql);
go

declare
@c int
begin
exec proc_getCount 'tb_students',@c output;
print @c;
end

drop proc proc_generalSplit;

create proc proc_generalSplit
@tableName varchar(50), --表名
@keyColumn varchar(30), --分页字段名（主键字段）
@condition varchar(100), --分页条件
@pageSize int, --每页显示的条数
@pageNum int, --显示第几页
@pageCount int output
as
declare @sql varchar(400);
declare @m int;
set @m = @pageSize*(@pageNum-1);
set @sql = 'select top('+convert(varchar,@pageSize)+') * from '+@tableName+' where '+@keyColumn+' not in
(select top('+convert(varchar,@m)+') '+@keyColumn+' from '+@tableName;
if(@condition!='')begin
set @sql = @sql+' where '+@condition+') and '+@condition;
end else begin
set @sql = @sql+')';
end
print @sql;
exec(@sql)
go

exec proc_generalSplit 'tb_students','stu_num','stu_age<25',3,1;

exec sp_help 'sp_executesql'

declare
@sql varchar(200),
@count int
begin
set @sql = 'select @count = count(*) from tb_students';
--exec(@sql);
exec sp_executesql @sql,N'@count int output',@count output;
print @count;
end

drop proc proc_generalSplit;

-----------------------------------------------------最终版分页

create proc proc_generalSplit
@tableName varchar(50), --表名
@keyColumn varchar(30), --分页字段名（主键字段）
@condition varchar(100), --分页条件
@pageSize int, --每页显示的条数
@pageNum int, --显示第几页
@pageCount int output,
@count int output
as

declare @sql0 nvarchar(100);
set @sql0 = 'select @c=count(*) from '+@tableName;
if(@condition!='')begin
set @sql0 = @sql0 + ' where ' +@condition;
end
exec sp_executesql @sql0,N'@c int output',@count output;

if(@count%@pageSize=0)begin
set @pageCount=@count/@pageSize;
end else begin
set @pageCount=@count/@pageSize+1;
end

if(@pageNum<1)begin
set @pageNum=1;
end
if(@pageNum>@pageCount)begin
set @pageNum=@pageCount;
end

declare @sql varchar(400);
declare @m int;
set @m=@pageSize*(@pageNum-1);

set @sql = 'select top('+convert(varchar,@pageSize)+') * from '+@tableName+' where '+@keyColumn+' not in
(select top('+convert(varchar,@m)+') '+@keyColumn+' from '+@tableName;

if(@condition!='')begin
set @sql = @sql+' where '+@condition+') and '+@condition;
end else begin
set @sql = @sql+')';
end

exec(@sql)

go

declare
@z int,
@m int;
begin
exec proc_generalSplit 'tb_students','stu_num','',3,1,@z output,@m output;
print @z;
print @m;
end

这篇关于SQL4的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！