22,207
社区成员
发帖
与我相关
我的任务
分享
declare @cur int
set @cur=1
declare @mod int
set @mod=1
declare @act int
set @act=1
declare @size int
set @size=10
exec sp_cursoropen @cur output, N'select * from fn_Company() where CompanyID=''XXX''', @mod output ,@act output,@size output
SELECT @cur as cur, @mod as mod, @act as act , @size as size
exec sp_cursorfetch @cur, 16, 1, 1
exec sp_cursorclose @cur
select CompanyID,
(select count(*) from T1) as Field1,
....(其他字段)
from Company
use tempdb
go
if OBJECT_ID('PageShow','P') is not null
drop procedure PageShow
go
Create procedure PageShow(
@QueryStr nvarchar(4000), --表名、视图名、查询语句
@Where nvarchar(1000)='',--条件
@PageSize int=10, --每页的大小(行数)
@PageCurrent int=1, --要显示的页
@FdShow nvarchar (4000)='', --要显示的字段列表
@FdOrder nvarchar (1000)='' --排序字段列表
)
as
if object_id(@QueryStr) is null
set @QueryStr='('+@QueryStr+')as Tab'
if @Where>''
set @Where=' where '+@Where
if @FdShow=''
set @FdShow='*'
if @FdOrder=''
set @FdOrder='(select 1)'
declare @Str nvarchar(max)
set @Str='select '+@FdShow+' from (select Row=Row_number()over(order by '+@FdOrder+'),'+@FdShow+
' from '+@QueryStr+@Where+') as Tab2 where Row between '+rtrim((@PageCurrent-1)*@PageSize+1)+' and '+rtrim(@PageCurrent*@PageSize)
exec(@Str)
go
exec PageShow @QueryStr='sysobjects',@FdShow='ID,Name',@PageCurrent=2,@Where=' ID>100 '
2.
select CompanyID,
(select count(*) from T1) as Field1,
....(其他字段)
from Company
这个company中有多少条记录,
子查询:(select count(*) from T1) as Field1 就会被执行多少次,效率太低了.
declare @ct int
select @ct= count(*) from T1
select CompanyID,
@ct as Field1,
....(其他字段)
from Company
这样用,明显会好很多.