Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

SQL 2005 ´æ´¢¹ý³Ì·ÖÒ³

create PROCEDURE [dbo].[P_PageTest]
    @SQL Nvarchar(max),  --SQLÓï¾ä²»°üÀ¨ÅÅÐò
    @CurPage int,    --µ±Ç°Ò³
    @PageRows int,    --Ò³Ãæ³ß´ç
 @Order Nvarchar(20),  --ÅÅÐò×Ö¶Î
 @OrderType Nvarchar(10), --ÅÅÐòÀàÐ͵¹Ðòdesc»òÕýÐòasc
    @TotalRecorder int output
AS
BEGIN
    SET NOCOUNT ON;
    declare @ExceSQL nvarchar(4000)
    --ÉèÖÿªÊ¼ÐкÅ
    declare  @start_row_num AS int
 declare  @end_row_num AS int
 if(@CurPage=1)
  begin
  SET @start_row_num = (@CurPage - 1) * @PageRows
  SET @end_row_num = @start_row_num+@PageRows
  end
 else
  begin
  SET @start_row_num = ((@CurPage - 1) * @PageRows)+1
  SET @end_row_num = (@start_row_num+@PageRows)-1
  end
    --ÉèÖñíʾ
    declare @RowNumber nvarchar(100)
    set @RowNumber = ', ROW_NUMBER() OVER(ORDER BY ' + @Order +' '+ @OrderType + ') as RowNumber from '
    set @SQL = Replace(@SQL,' from ',@RowNumber)
    --»ñµÃ×ܼǼÊý
    set @ExceSQL = 'WITH tmp AS (' + @SQL + ')
        select @TotalRecorder=max(RowNumber) from tmp'
    execute sp_executesql @ExceSQL,N'@TotalRecorder int output',@TotalRecorder output
    --²éѯÓï¾ä
    set @ExceSQL = 'WITH tmp AS (' + @SQL + ')
        select * from tmp where RowNumber between ' + Convert(nvarchar,@start_row_num)
        + ' And ' + Convert(nvarchar,@end_row_num)
    execute(@ExceSQL)
END


Ïà¹ØÎĵµ£º

SQLÉí·ÝÖ¤¸ñʽÑéÖ¤


SQL°æ±¾Éí·ÝÖ¤¸ñʽÑéÖ¤ 0Ϊ´íÎó£¬1ΪÕýÈ·
CREATE  function [dbo].[IsID](@str nvarchar(18),@sex int)--´«ÈëÁ½¸ö±äÁ¿ Éí·ÝÖ¤ºÍÐÔ±ð(ÄÐ1Å®2)
returns bit
as
begin
declare @len int
declare @result bit
--set @result=0
set @len=len(@str)
if(@len!=18 and @len!=15)--³¤¶ÈÊÇ18λ»ò15λ
 begin
& ......

SQL Select N to M Records (single Table)

È¡±íÀïnµ½mÌõ¼Í¼µÄ¼¸ÖÖ·½·¨:
1. Ö»ÐèÒª²éѯǰMÌõÊý¾Ý(0 to M),
1.1 ʹÓà top(M) ·½·¨:
select top(3) * from [tablename]
 
1.2 ʹÓà set rowcount ·½·¨:
http://msdn.microsoft.com/zh-cn/library/ms188774(SQL.90).aspx
set rowcount M
select * from [tablename]
set rowcount 0
ȨÏÞ ÒªÇó¾ßÓÐ public ......

SQL²éѯЧÂÊ 100wÊý¾Ý²éѯֻҪ1Ãë

»úÆ÷Çé¿ö
p4: 2.4
ÄÚ´æ: 1 G
os: windows 2003
Êý¾Ý¿â: ms sql server 2000
Ä¿µÄ: ²éѯÐÔÄܲâÊÔ,±È½ÏÁ½ÖÖ²éѯµÄÐÔÄÜ
SQL²éѯЧÂÊ step by step
-- setp 1.
-- ½¨±í
create table t_userinfo
(
userid int identity(1,1) primary key nonclustered,
nick&nbs ......

SQL ServerÊý¾Ý¿â¸÷¶ÔÏóµÄ×î´óÈÝÁ¿ËµÃ÷

ϱí˵Ã÷ÔÚ Microsoft SQL Server Êý¾Ý¿âÖж¨ÒåµÄ£¬»òÔÚ Transact-SQL
Óï¾äÖÐÒýÓõĸ÷ÖÖ¶ÔÏóµÄ×î´óÖµ£¨ÊýÁ¿»ò´óС£©¡£ÏÂ±í²»°üº¬ Microsoft® SQL Server 2000™ Windows® CE °æ¡£
 
×î´óÖµ£¨ÊýÁ¿»ò´óС£©
¶ÔÏó
SQL Server 7.0
SQL Server 2000
Åú´¦Àí´óС
65,536 * ÍøÂçÊý¾Ý°ü´óС1
65,536 * Í ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ