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
Ïà¹ØÎĵµ£º
¹¤¾ß£º
Sybase PowerDesigner 12.5
Microsoft SQL Server 2005
µÚÒ»²½£º¸ÅÒªÉè¼Æ
´ò¿ªPowerDesignerÈí¼þ£¬Éè¼Æ“¸ÅÄîÊý¾ÝÄ£ÐÍ”(Conceptual Data Model)£º
ͼ1
Éè¼Æ±í½á¹¹£º
ͼ2
ÔÚÉè¼ÆÊôÐÔ(×Ö¶Î)µÄʱºò£¬Èý¸ö×Öĸ(M¡¢P¡¢D)·Ö±ð±íʾ£º
M£ºÊÇ·ñΪ¿Õ;(√±íʾ²»ÔÊÐíΪ¿Õ)
P£ºÊÇ·ñ뻅 ......
ÈçºÎÓÅ»¯SQL Server
1¡¢ ÓóÌÐòÖУ¬±£Ö¤ÔÚʵÏÖ¹¦ÄܵĻù´¡ÉÏ£¬¾¡Á¿¼õÉÙ¶ÔÊý¾Ý¿âµÄ·ÃÎÊ´ÎÊý£»Í¨¹ýËÑË÷²ÎÊý£¬¾¡Á¿¼õÉÙ¶Ô±íµÄ·ÃÎÊÐÐÊý,×îС»¯½á¹û¼¯£¬´Ó¶ø¼õÇáÍøÂ縺µ££»Äܹ»·Ö¿ªµÄ ²Ù×÷¾¡Á¿·Ö¿ª´¦Àí£¬Ìá¸ßÿ´ÎµÄÏìÓ¦ËÙ¶È£»ÔÚÊý¾Ý´°¿ÚʹÓÃSQLʱ£¬¾¡Á¿°ÑʹÓõÄË÷Òý·ÅÔÚÑ¡ÔñµÄÊ×ÁУ»Ëã·¨µÄ½á¹¹¾¡Á¿¼òµ¥£»ÔÚ²éѯʱ£¬²»Òª¹ý¶ ......
SQLÓÅ»¯²éѯ
ÊÕ²Ø
Êý¾Ý¿âµÄ²éѯÓÅ»¯¼¼Êõ
Êý¾Ý¿âϵͳÊǹÜÀíÐÅϢϵͳµÄºËÐÄ£¬»ùÓÚÊý¾Ý¿âµÄÁª»úÊÂÎñ´¦Àí£¨OLTP£©ÒÔ¼°Áª»ú·ÖÎö´¦Àí(OLAP)ÊÇÒøÐС¢ÆóÒµ¡¢Õþ¸®µÈ²¿ÃÅ×îÎªÖØÒªµÄ¼ÆËã»úÓ¦
ÓÃÖ®Ò»¡£´Ó´ó¶à ......
1. ¸´ÖƱí½á¹¹
Sql´úÂë
1. select * into B from A where 1=0;
select * into B from A where 1=0;
2.¸´ÖƱí¼Ç¼ ¸´ÖÆÄ³Ð©×Ö¶Î
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;
¸´Ö ......
н¨±í£º
create table [±íÃû]
(
[×Ô¶¯±àºÅ×Ö¶Î] int IDENTITY (1,1) PRIMARY KEY ,
[×Ö¶Î1] nVarChar(50) default 'ĬÈÏÖµ' null ,
[×Ö¶Î2] ntext null ,
[×Ö¶Î3] datetime,
[×Ö¶Î4] money null ,
[×Ö¶Î5] int default 0,
[×Ö¶Î6] Decimal (12,4) default 0,
[×Ö¶Î7] image null ,
)
ɾ³ý±í£º
Drop table [±í ......