sp_getselectrecord.sql

来自「该存储过程用于用户查询某个表特定一个区间数据的时候用到」· SQL 代码 · 共 79 行

IF OBJECT_ID (N'dbo.Sp_GetSelectRecord', N'P') IS NOT NULL
    DROP Proc dbo.Sp_GetSelectRecord;
GO

Create Proc Sp_GetSelectRecord
(
@TblName	sysname,				--表示要查询的表名
@TmpTblName	sysname,				--表示一个临时表，可以随意命名，为自己程序而加的参数，可以修改代码把该参数删除
@BeginIndex	Int,					--表示从第几行开始
@CountIndex	Int,					--表示共几行
@StrFields	varchar(100)=''		--表示需要显示的字段,默认为全部字段
)
AS
Set Nocount On

Declare @Colstat int,
	@Name varchar(50),
	@Fields varchar(4000),
	@Sql nvarchar(4000),
	@TmpSql nvarchar(4000),
	@Rcount	int

If @BeginIndex<0
Begin
	Raiserror 28000 '''BeginIndex'' must be big at 0.'
	Return
End

If @CountIndex<=0
Begin
	Raiserror 28000 '''CountIndex'' must be big at 0.'
	Return
End

IF OBJECT_ID (N'dbo.'+@TmpTblName, N'U') IS NOT NULL
Begin
    	Set @TmpSql=N'Drop Table '+@TmpTblName
	Exec sp_executesql @TmpSql
End
	
Set @Sql=''
Set @Fields=''
Set @Rcount=@BeginIndex+@CountIndex

Declare TblFields Cursor For Select Name,Colstat From SysColumns Where id in(Select id From SysObjects Where Name=@TblName) and colstat=0
Open TblFields

Fetch Next From TblFields Into @Name,@Colstat
While @@FETCH_STATUS = 0
Begin
	--Select @Name
	if @StrFields=''
		Select @Fields=@Fields+@Name+','
	else
		Select @Fields=@StrFields+','
	Fetch Next From TblFields Into @Name,@Colstat
End

Close TblFields
Deallocate TblFields

Set @Fields=substring(@Fields,1,len(@Fields)-1)

Set @Sql=N'Select Top '+Cast(@Rcount as nvarchar(10))+N' id=Identity(int,1,1),'+@Fields+N' Into '+@TmpTblName+' From '+@TblName
Exec sp_executesql @Sql

Set @Sql=N'Select Top '+Cast(@CountIndex as nvarchar(10))+@Fields+N' From '+@TmpTblName+' Where id Not IN (Select Top '+Cast(@BeginIndex as nvarchar(10))+N' id From '+@TmpTblName+')'
Exec sp_executesql @Sql

Set @TmpSql=N'Drop Table '+@TmpTblName
Exec sp_executesql @TmpSql

GO

--Exec Sp_GetSelectRecord instrument,aa,10,2,'instrument,market'



--Select * From A111 Where a not in(Select Top 10 a From A111)