/ * - Display the page X page of the specified table, view, and query results with the stored procedure, and extract the primary key or identity column directly from the original table, other cases, if the view Or the primary key in the query results, do not recommend this method - Zou Jian 2003.09 - * // * - Call example exec p_show 'Area Exec P_SHOW' Area Information ', 5, 3,' Area number, region name, Assisted Decod ',' Area No. '- * // * Because it is necessary to take into account versatility, there is a certain amount of query, if you are sorted, then the result is: Exec p_show' SELECT TOP 100 percent * From regional information Order By Region Name ', 5, 3,' Area Number, Region Name, Assist Code ',' Area Name '- Query Statement Plus: Top 100 Percent // Top * / if EXISTS (SELECT * from dbo.sysobjects where id = object_id (N '[dbo]. [p_show]') and OBJECTPROPERTY (id, N'IsProcedure ') = 1) drop procedure [dbo]. [p_show] GOCREATE Proc p_show @ queryStr nvarchar (4000 ), - Table name, view name, query statement @PageSize int = 10, - The size of each page (number of lines) @PAGECURRENT INT = 1, - To display the page @fdshow nvarchar (4000) = '', - To display the list of fields, if the query result is identified by the identity field, you need to specify this value without including the identification field @fdorder nvarchar (1000) = '' - Sort List AsDeclare @fdname nvarchar (250) - Table Primary key or table, Identification column name in the temporary table, @ id1 varchar (20), @ id2 varchar (20) - start and end record number, @ obj_id int - object ID - table has a composite primary key Handling Declare @strfd nvarchar (2000) - Composite primary key list, @ strjoin nvarchar (4000) - Connection field, @ Strwhere nvarchar (2000) - Query Condition Select @ obj_id = Object_ID (@querystr), @ fdshow = case Isnull (@fdshow, '') when '' '@' else '' @ fdshow end, @ fdorder = case isnull (@fdorder, '') when '' Ten 'Else' Order By ' @ fdorder END , @ Querystr = case when @obj_id is not null damj ' @ querystr else' (' @ querystr ') a 'end - If the first page is displayed, you can use top to complete the IF @PAgeCurrent = 1 Begin SELECT @ Id1 = CAST (@PageSize As Varchar (20)) EXEC ('SELECT TOP' @ ID1 @ fdshow "
From ' @ querystr @ fdorder) Returnend- If you are a table, check if there is a logo or the primary key if @obj_id is not null and objectprproperty (@ obj_id,' istable ') = 1begin select @ id1 = Cast (@ PageSize as varchar (20) - If there is no marking column in the table, check if there is a primary key policy if not exists in the table (SELECT 1 from sysobjects where parent_obj = @ obj_id and xtype = 'pk') goto lbuseTemp - If there is no primary key in the table, use temporary table processing select @ FdName = name from syscolumns where id = @ obj_ID and colid in (select colid from sysindexkeys where @ obj_ID = id and indid in (select indid from sysindexes where @ obj_ID = id and name in (select name from sysobjects where xtype = 'PK' and PARENT_OBJ = @ obj_id))) IF @@ rowcount> 1 - Check if the primary key in the table is a composite primary key becom selection @Strfd = '', @ strjoin = '', @ Strwhere = '' SELECT @ Strfd = @ Strfd ', [' Name ']', @ Strjoin = @ Strjoin 'and a. [' name '] = b. [' name '], @ strwhere = @ Strwhere ' and b. [ ' Name '] Is Null 'from syscolumns where id = @ obj_id and color in (Select Colid from SysIndexke ys where @ Obj_ID = id and indid in (select indid from sysindexes where @ Obj_ID = id and name in (select name from sysobjects where xtype = 'PK' and parent_obj = @ Obj_ID))) select @ strfd = substring (@strfd, 2,2000, @ strjoin, 5,4000), @ Strwhere = Substring (@
