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°æ±¾Éí·ÝÖ¤¸ñʽÑéÖ¤ 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
& ......
È¡±íÀï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 ......
»úÆ÷Çé¿ö
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 ......
ϱí˵Ã÷ÔÚ Microsoft SQL Server Êý¾Ý¿âÖж¨ÒåµÄ£¬»òÔÚ Transact-SQL
Óï¾äÖÐÒýÓõĸ÷ÖÖ¶ÔÏóµÄ×î´óÖµ£¨ÊýÁ¿»ò´óС£©¡£ÏÂ±í²»°üº¬ Microsoft® SQL Server 2000™ Windows® CE °æ¡£
×î´óÖµ£¨ÊýÁ¿»ò´óС£©
¶ÔÏó
SQL Server 7.0
SQL Server 2000
Åú´¦Àí´óС
65,536 * ÍøÂçÊý¾Ý°ü´óС1
65,536 * Í ......