SQL SERVER 2005·ÖÒ³´æ´¢¹ý³Ì
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[PE011_Page]
@TableName varchar(50), --±íÃû
@Fields varchar(5000) = '*', --×Ö¶ÎÃû(È«²¿×Ö¶ÎΪ*)
@OrderField varchar(5000), --ÅÅÐò×Ö¶Î(±ØÐë!Ö§³Ö¶à×Ö¶Î)
@sqlWhere varchar(5000) = Null,--Ìõ¼þÓï¾ä(²»ÓüÓwhere)
@pageSize int, --ÿҳ¶àÉÙÌõ¼Ç¼
@pageIndex int = 1 , --Ö¸¶¨µ±Ç°ÎªµÚ¼¸Ò³
@totalRecord int = 0 output,
@TotalPage int output --·µ»Ø×ÜÒ³Êý
AS
BEGIN
Begin Tran --¿ªÊ¼ÊÂÎñ
Declare @sql nvarchar(4000);
if @totalRecord<=0 begin
--¼ÆËã×ܼǼÊý
if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'select @totalRecord = count(*) from ' + @TableName
else
set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' with(nolock) where ' + @sqlWhere
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--¼ÆËã×ܼǼÊý
end
--¼ÆËã×ÜÒ³Êý
select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'Select * from (select ROW_NUMBER
Ïà¹ØÎĵµ£º
Êý¾Ý¿â¿ª·¢¹ý³ÌÖУ¬µ±Äã¼ìË÷µÄÊý¾ÝÖ»ÊÇÒ»Ìõ¼Ç¼ʱ£¬ÄãËù±àдµÄÊÂÎñÓï¾ä´úÂëÍùÍùʹÓÃSELECT INSERT Óï¾ä¡£µ«ÊÇÎÒÃdz£³£»áÓöµ½ÕâÑùÇé¿ö£¬¼´´Óijһ½á¹û¼¯ÖÐÖðÒ»µØ¶ÁÈ¡Ò»Ìõ¼Ç¼¡£ÄÇôÈçºÎ½â¾öÕâÖÖÎÊÌâÄØ£¿ÓαêΪÎÒÃÇÌṩÁËÒ»ÖÖ¼«ÎªÓÅÐãµÄ½â¾ö·½°¸¡£
1.1 ÓαêºÍÓαêµÄÓŵã
& ......
declare @p int
declare @p1 int
declare @count int
set @p=0
set @p1=10
set @count=2
if(@count<>0 or @count<>1)
set @p=@p1*@count-10
SELECT [t1].[userid], [t1].[username], [t1].[userorder]
from (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[userorder]) AS [ROW_NU ......
1. ¸´ÖƱí½á¹¹
Sql´úÂë
1. select * into B from A where 1=0;
select * into B from A where 1=0;
2.¸´ÖƱí¼Ç¼ ¸´ÖÆijЩ×Ö¶Î
Sql´úÂë
1. insert into B(a, b, c) select d, e, f from A;
insert into B(a, b, c) select d, e, f from A;
¸´Ö ......
Ò»¡¢»ù´¡
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CREATE DATABASE database-name
2¡¢ËµÃ÷£ºÉ¾³ýÊý¾Ý¿â
drop database dbname
3¡¢ËµÃ÷£º±¸·Ýsql server
--- ´´½¨ ±¸·ÝÊý¾ÝµÄ device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- ¿ªÊ¼ ±¸·Ý
BACKUP DATABASE pubs TO testBac ......
ÕâÊÇCSDNÂÛ̳ÀïÃæµÄÒ»¸öÌû×Ó£¬×÷ÕßÊÇ°®Ð¾õÂÞ.ع»ª¡£¸Ð¾õ±È½ÏÈ«Ã棬¾ÍÕûÀíÏÂÀ´ÁË£¬Áô×÷ÒÔºó²Î¿¼±¸Óá£ÔÚ´Ë£¬Ôڴ˸Ðл±¾ÎĵÄ×÷Õß¡£
/*
±êÌ⣺һ¸öÏîÄ¿Éæ¼°µ½µÄ50¸öSqlÓï¾ä(ÕûÀí°æ)
×÷Õߣº°®Ð¾õÂÞ.ع»ª(Ê®°ËÄê·çÓ꣬ÊصñùɽѩÁ«»¨¿ª)
ʱ¼ä£º2010-05-10
µØµã£ºÖØÇ캽ÌìְҵѧԺ
˵Ã÷£ºÒÔÏÂÎåÊ®¸öÓï¾ä¶¼°´ÕÕ²âÊÔÊý¾Ý½øÐ ......