Microsoft SqlServer 2005 ͨÓ÷ÖÒ³ ´æ´¢¹ý³Ì
±¾À´ÎÒÊDz»ÔÞ³ÉʹÓÃͨÓô洢¹ý³ÌµÄ£¬Ö÷ÒªÊÇÒòΪ¸ù¾Ý±í½á¹¹À´¶¨ÖÆ·ÖÒ³²éѯ²»Óö¯Ì¬µÄÆ´SQL£¬ÕâÑù²ÅÊÇÕæÕýµÄ¸ßЧ£¬¶øÇÒֻҪд¹ýÒ»¸ö£¬ÄÇôÔÙÓÐÐÂÐèÇóµÄʱºò£¬Ð¡·¶Î§¸Ä¶¯¼¸´¦¾ÍokÁË¡£
µ«×ÜÊÇÓÐÈËÏòÎÒÌÖÒª»òÕßÌÖÂÛͨÓô洢¹ý³Ì£¬Ã»°ì·¨£¬±»±ÆÎÞÄΣ¬Á¼ÐÄÉ¥ÓëÀ§¾³¡£
ľÓÐÕÒµ½T-SQL´úÂë±à¼Æ÷
-- =============================================
-- Author: Ô¬Á¢Ç¿
-- Create date: 2006-5-11
-- Description: SQL 2005 °æ±¾ ͨÓ÷ÖÒ³´æ´¢¹ý³Ì
-- For: FirstDM
-- BLOG: http://blog.csdn.net/ylqmf
-- =============================================
CREATE PROCEDURE [dbo].[Pagination]
@Table nvarchar(4000) = N'select * from sys.objects',
@ID nvarchar(200) = N'[object_id]',
@OrderBy nvarchar(2000) = N'[object_id]',
@PageSize int = 20,
@PageIndex int = 1,
@RowCount int OUTPUT
as
if @PageIndex<1
begin
set @PageIndex=1;
end
if @PageSize<1
begin
set @PageSize = 20;
end
declare @begin int
declare @end int
set @begin=(@PageIndex-1)*@PageSize+1;
set @end = @PageIndex*@PageSize;
declare @from nvarchar(4000)
declare @sql nvarchar(4000)
declare @index int
select @index = PATINDEX(N'%from%',@table);
if @index = 0
begin
set @from = N'from '+@Table;
set @sql= N'select * from '+@Table;
end
else
begin
set @from = SUBSTRING(@table,@index,LEN(@table)-@index+1);
set @sql = @Table;
end
declare @parameters nvarchar(50)
set @parameters = '@RowCount int OUTPUT';
declare @SQLString nvarchar(max)
select @SQLString =N'
declare @Tpage table
(
id bigint,
inx bigint PRIMARY KEY
);
with cte_page
as
(
select '+@id+N' as id,ROW_NUMBER() over (order by '+@orderby+N') as inx '+@from+N'
)
insert @Tpage select * from cte_page;
with cte_temp
as
(
'+@sql+N'
)
select a.* from cte_temp as a
inner join @Tpage as b on b.id = a.'+@ID+N'
where b.inx between '+CONVERT(nvarchar(100), @begin)+N' and '+CONVERT(nvarchar(100), @end)+N';
with cte_count
as
(
select top 1 inx from @Tpage order by inx desc
)
select @RowCount = inx from cte_count;
'
exec sp_executesql @SQLString , @
Ïà¹ØÎĵµ£º
INSERT´¥·¢Æ÷
INSERT¼°UPDATE´¥·¢Æ÷¾³£ÓÃÓÚ¼ì²â´¥·¢Æ÷Ëù¼à¿Ø±íµÄÁм°ÆäÊý¾ÝÊÇ·ñ·ûºÏËù¶¨ÒåµÄ¹æÔò¡£ËüÃÇ¿ÉÒÔÔÚÊý¾ÝÊäÈë±í֮ǰ£¬¶ÔÆä½øÐÐÔÚ¶¨ÒåÒýÓÃÍêÕûÐÔʱÎÞ·¨Íê³ÉµÄÔ¼Êø¼ìÑé¡£
ÏÂÃæÒÔѧÉúÊý¾Ý¿âstudentΪÀýÀ´½éÉÜINSERT´¥·¢Æ÷µÄʹÓ᣸ÃÊý¾Ý¿â°üÀ¨Èý¸ö±í£¬·Ö±ðÊÇÃèÊöѧÉúÇ ......
SQLServer
: EXECºÍsp_executesqlµÄÇø±ð
ÕªÒª
1,EXECµÄʹÓÃ
2£¬sp_executesqlµÄʹÓÃ
MSSQLΪÎÒÃÇÌṩÁËÁ½ÖÖ¶¯Ì¬Ö´ÐÐSQLÓï¾äµÄÃüÁ·Ö±ðÊÇEXECºÍsp_executesql;ͨ³£,sp_executesqlÔò¸ü¾ßÓÐÓÅÊÆ£¬Ëü
ÌṩÁËÊäÈëÊä³ö½Ó¿Ú£¬¶øEXECûÓС£»¹ÓÐÒ»¸ö×î´óµÄºÃ´¦¾ÍÊÇÀûÓÃsp_ ......
select identity(int,1,1) as col_id , * into temp from uep.dbo.A_experiment
select * from temp where col_id between 50 and 60
drop table temp ......
Access¡¢SQLServer¡¢Oracle³£¼ûSQLÓï¾äÓ¦ÓÃÇø±ð
¹Ø¾¢ËÉ PMP
Èç¹ûÒª¼æÈÝAccess¡¢SQL Server¡¢OracleÈý¸öÊý¾Ý¿â°æ±¾£»ÎÒÃÇÔÚ±àдSQLÓï¾äµÄ¹ý³ÌÖУ¬¾¡Á¿Ê¹ÓÃһЩͨÓõÄÓï¾ä£¬µ«»¹ÊÇÓÐЩ·½ÃæÓÐЩÇø±ð±ÜÃâ²»ÁË£¬ÏÖ¼òµ¥×ܽáһϡ£
ÒÔÏÂA´ú±íAccess,S´ú±íSQL Server,O´ú±íOracle
1¡¢È¡µ±Ç°ÏµÍ³Ê±¼ä
A:Select Now()
S:Selec ......