sql ´æ´¢¹ý³Ì ·ÖÒ³
-- FUN:´æ´¢¹ý³Ì·ÖÒ³
-- @Table nvarchar(255), -- ±íÃû
-- @Fields nvarchar(1000) = ' * ', -- ÐèÒª·µ»ØµÄÁÐ
-- @OrderField nvarchar(255), -- ÅÅÐòµÄ×Ö¶ÎÃû,Ò»°ãΪΨһ±êʶ
-- @OrderType bit = 0, -- ÉèÖÃÅÅÐòÀàÐÍ, ·Ç 0 ÖµÔò½µÐò
-- @PageSize int = 10, -- ÿҳÓжàÉÙÌõ¼Ç¼
-- @PageIndex int = 1, -- µÚ¼¸Ò³
-- @Count int output, -- ·µ»Ø¼Ç¼×ÜÊý
-- @StrWhere nvarchar(1500) -- ²éѯÌõ¼þ (×¢Òâ: ²»Òª¼Ó where,³õʼ»¯Îª1=1ʹµÃ²éѯÌõ¼þÓÀÔ¶²»Îª¿Õ)
----------------------------------------------------------
ALTER PROC [dbo].[PR_pages]
(
@Table nvarchar(4000), -- ±íÃû
@Fields nvarchar(1000) = ' * ', -- ÐèÒª·µ»ØµÄÁÐ
@OrderField nvarchar(255), -- ÅÅÐòµÄ×Ö¶ÎÃû,Ò»°ãΪΨһ±êʶ
@OrderType bit = 0, -- ÉèÖÃÅÅÐòÀàÐÍ, ·Ç 0 ÖµÔò½µÐò
@PageSize int = 10, -- ÿҳÓжàÉÙÌõ¼Ç¼
@PageIndex int = 1, -- µÚ¼¸Ò³
@StrWhere nvarchar(1500), -- ²éѯÌõ¼þ (×¢Òâ: ²»Òª¼Ó where,³õʼ»¯Îª1=1ʹµÃ²éѯÌõ¼þÓÀÔ¶²»Îª¿Õ)
@Count int output -- ·µ»Ø¼Ç¼×ÜÊý
)
AS
BEGIN
DECLARE @StrSQL nvarchar(4000) -- Ö÷Óï¾ä
DECLARE @StrTmp nvarchar(110) -- ÁÙʱ±äÁ¿
DECLARE @StrOrder nvarchar(400) -- ÅÅÐòÀàÐÍ
DECLARE @SumSql nvarchar(4000) -- ¼Ç¼×ÜÊý
--SET @StrWhere = ' 1=1 '
-- Ö´ÐÐ×ÜÊýͳ¼Æ
SET @SumSql = 'SELECT @Count = COUNT(0) from ' + @Table + ' WHERE 1=1 ' + @StrWhere
EXEC sp_executesql @SumSql,N'@Count int output',@Count output -- ¼Ç¼×ÜÊý
-- Èç¹û@OrderType²»ÊÇ0£¬¾ÍÖ´ÐнµÐò
IF @OrderType != 0
BEGIN
SET @StrTmp = ' <(SELECT MIN'
SET @StrOrder = ' ORDER BY [' + @OrderField + '] DESC'
END
ELSE
BEGIN
SET @StrTmp = ' >(SELECT MAX'
SET @StrOrder = ' ORDER BY [' + @OrderField + '] ASC'
END
-- Èç¹ûÊǵÚÒ»Ò³¾ÍÖ´ÐÐÒÔÏ´úÂ룬¼Ó¿ìÖ´ÐÐËÙ¶È
IF @PageIndex = 1
BEGIN
SET @StrSQL = 'SELECT TOP ' + STR(@Pag
Ïà¹ØÎĵµ£º
----start
¶¯Ì¬SQLÊÇÔÚ³ÌÐòÔËÐÐʱ¹¹ÔìµÄ£¬ÒªÖ´Ðе¥ÌõSQL£¬Ê¹ÓÃEXECUTE IMMEDATE Óï¾ä£»µ±ÅúÁ¿Ö´ÐÐSQLʱ£¬ÏÈʹÓÃPREPARE Óï¾ä¹¹ÔìSQL£¬È»ºóʹÓÃEXECUTE Óï¾äÖ´ÐС£
Ò»£ºPrepareÓï¾ä£ºÓÃÀ´¹¹ÔìÅúÁ¿SQL
Óï·¨£º
PREPARE <sql-statement> [OUTPUT] INTO <result> [INPUT INTO] <input> ......
ÆÕͨÐÐÁÐת»»
ÎÊÌ⣺¼ÙÉèÓÐÕÅѧÉú³É¼¨±í(tb)ÈçÏÂ:
ÐÕÃû ¿Î³Ì ·ÖÊý
ÕÅÈý ÓïÎÄ 74
ÕÅÈý Êýѧ 83
ÕÅÈý ÎïÀí 93
ÀîËÄ ÓïÎÄ 74
ÀîËÄ Êýѧ 84
ÀîËÄ ÎïÀí 94
Ïë±ä³É(µÃµ½ÈçϽá¹û)£º
ÐÕÃû ÓïÎÄ Êýѧ ÎïÀí
---- ---- ---- ----
ÀîËÄ 74 84 94
ÕÅÈý 74 83 93
-------------------
*/
create table tb(Ð ......
Cross Applyʹ±í¿ÉÒԺͱíÖµº¯Êý½á¹û½øÐÐjoin, ÕâÑù±íÖµº¯ÊýµÄ²ÎÊý¾Í¿ÉÒÔʹÓÃÒ»¸ö½á¹û¼¯£¬¶ø²»ÊÇÒ»¸ö±êÁ¿Öµ£¬ÏÂÃæÊÇbook onlineµÄÔÎÄ£¬ÓÐÀý×Ó£¬ÓнâÊÍ¡£
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function act ......
ÔÎĵØÖ·£ºhttp://www.cnblogs.com/changhai0605/articles/1276319.html
OracleµÄÇë²Î¿¼£ºhttp://zonghl8006.blog.163.com/blog/static/4528311520083995931317/
1.¼ò½é£º
SQL Server 2005ÖÐÐÂÔöµÄ´°¿Úº¯Êý°ïÖúÄãѸËٲ鿴²»Í¬¼¶±ðµÄ¾ÛºÏ£¬Í¨¹ýËü¿ÉÒԷdz£·½±ãµØÀÛ¼Æ×ÜÊý¡¢Òƶ¯Æ½¾ùÖµ¡¢ÒÔ¼°Ö´ÐÐÆäËü¼ÆËã¡£
´°¿Úº¯Êý¹¦ÄÜ·Ç ......
SQL Server ϵͳȫ¾Ö±äÁ¿
@@CONNECTIONS
·µ»Ø×ÔÉÏ´ÎÆô¶¯ÒÔÀ´Á¬½Ó»òÊÔͼÁ¬½ÓµÄ´ÎÊý¡£
@@CURSOR_ROWS
·µ»ØÁ¬½ÓÉÏ×îºó´ò¿ªµÄÓαêÖе±Ç°´æÔڵĺϸñÐеÄÊýÁ¿(·µ»Ø±»´ò¿ªµÄÓαêÖл¹Î´±»¶ÁÈ¡µÄÓÐЧÊý¾ÝÐеÄÐÐÊý)
@@DATEFIRST
·µ»ØÿÖܵÚÒ»ÌìµÄÊý×Ö
@@ERROR
·µ»Ø×îºóÖ´ÐеÄSQL Óï¾äµÄ´íÎó´úÂë¡£
@@FETCH_STATUS
·µ ......