sql server ´æ´¢¹ý³ÌʵÏÖ·ÖÒ³
ÔÚ¿ª·¢²»ÂÛÊÇWEB»¹ÊÇWinform³ÌÐòÖУ¬Êý¾Ý·ÖÒ³ÊǾ³£Óöµ½µÄÎÊÌ⣬ҪÊÇÔÚ´úÂëÖÐʵÏִ˹ý³Ì£¬ÏÔÈ»½µµÍÁË·þÎñÆ÷µÄЧÂÊ£¬¿ÉÒÔʹÓÃÒÔÏ´洢¹ý³ÌÀ´ÊµÏÖ¡£
CREATE PROCEDURE pagination
@tblName varchar(255), -- ±íÃû
@strGetFields varchar(1000) = '*', -- ÐèÒª·µ»ØµÄÁÐ
@fldName varchar(255)='', -- ÅÅÐòµÄ×Ö¶ÎÃû
@PageSize int = 10, -- Ò³³ß´ç
@PageIndex int = 1, -- Ò³Âë
@doCount bit = 0, -- ·µ»Ø¼Ç¼×ÜÊý, ·Ç 0 ÖµÔò·µ»Ø
@OrderType bit = 0, -- ÉèÖÃÅÅÐòÀàÐÍ, ·Ç 0 ÖµÔò½µÐò
@strWhere varchar(1500) = '' -- ²éѯÌõ¼þ (×¢Òâ: ²»Òª¼Ó where)
AS
declare @strSQL varchar(5000) -- Ö÷Óï¾ä
declare @strTmp varchar(110) -- ÁÙʱ±äÁ¿
declare @strOrder varchar(400) -- ÅÅÐòÀàÐÍ
if @doCount != 0
begin
if @strWhere !=''
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where '+@strWhere
else
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end
--ÒÔÉÏ´úÂëµÄÒâ˼ÊÇÈç¹û@doCount´«µÝ¹ýÀ´µÄ²»ÊÇ0£¬¾ÍÖ´ÐÐ×ÜÊýͳ¼Æ¡£ÒÔϵÄËùÓдúÂ붼ÊÇ@doCountΪ0µÄÇé¿ö
else
begin
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName +'] desc'
--Èç¹û@OrderType²»ÊÇ0£¬¾ÍÖ´ÐнµÐò£¬Õâ¾äºÜÖØÒª£¡
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end
if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['+ @tblName + '] '+ @strOrder
--Èç¹ûÊǵÚÒ»Ò³¾ÍÖ´ÐÐÒÔÉÏ´úÂ룬ÕâÑù»á¼Ó¿ìÖ´ÐÐËÙ¶È
end
else
begin
--ÒÔÏ´úÂ븳ÓèÁË@strSQLÒÔÕæÕýÖ´ÐеÄSQL´úÂë
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'+ @strOrder
if @st
Ïà¹ØÎĵµ£º
ÎÞ·¨´ò¿ªÓû§Ä¬ÈÏÊý¾Ý¿â£¬µÇ¼ʧ°Ü,ÕâÊÇSQL ServerʹÓÃÕßÊìϤµÄÎÊÌâÖ®Ò»¡£ÔÚʹÓÃÆóÒµ¹ÜÀíÆ÷¡¢²éѯ·ÖÎöÆ÷¡¢¸÷À๤¾ßºÍÓ¦ÓÃÈí¼þµÄʱºò£¬Ö»Òª¹Øϵµ½Á¬½ÓSQL ServerÊý¾Ý¿âµÄʱºò£¬¶¼ÓпÉÄÜ»áÅöµ½´ËÎÊÌâ,ÒýÆð´Ë´íÎó·¢ÉúµÄÔÒò±È½Ï¶à,ÏÂÃæÎÒÃǾÍÀ´Ïêϸ·ÖÎöÒýÆð´ËÎÊÌâµÄÔÒòÒÔ¼°½â¾ö°ì·¨¡£
Ò»¡¢ÔÒò
µÇ¼ÕÊ»§µÄĬÈÏÊý¾Ý¿â±»É¾³ý¡ ......
1. ´ò¿ªpl\sql ÔËÐÐÏÂÃæµÄ½Å±¾£¬²éѯ·þÎñÆ÷µÄ×Ö·û¼¯ ¡£
SELECT 'AMERICAN_AMERICA.' || PROPERTY_VALUE
from DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'NLS_CHARACTERSET';
ÎÒµÄÊÇ AMERICAN_AMERICA.ZHS16GBK
2.ÔÚÔËÐÐÖÐÊäÈ룺regedit£¬´ò¿ª×¢²á±í±à¼Æ÷£»
3.HKEY_LOCAL_MACHI ......
Ç°ÑÔ
Å©ÒµÒøÐÐ×ÜÐÐ1998ÄêÒÔÀ´ÕýʽÍƹãÁËаæÍøÂç°æ×ÛºÏÒµÎñͳ¼ÆÐÅϢϵͳ£¬¸ÃϵͳÊÇ»ùÓÚWindowsNT4.0ƽ̨£¬²ÉÓÿͻ§£¯·þÎñÆ÷ģʽ£¬ÒÔMicrosoft SQL ServerΪ»ù´¡½¨Á¢ÆðÀ´µÄ´óÐÍÊý¾Ý¿âÓ¦ÓóÌÐò£¬ÏµÍ³½çÃæÓѺᢲÙ×÷¼ò±ã£¬¼ÆËã¡¢·ÖÎö¡¢¼ìË÷¹¦Äܷdz£Ç¿´ó£¬Îª±£Ö¤Å©ÒµÒøÐÐϵͳ¼°Ê±½øÐÐ×ÝÏòºÍºáÏòÒµÎñÊý¾Ý²É¼¯¡¢°´ÕÕ²»Í¬ÒªÇóÉú³ ......
sysaltfiles Ö÷Êý¾Ý¿â ±£´æÊý¾Ý¿âµÄÎļþ
syscharsets Ö÷Êý¾Ý¿â &nb ......
--×÷ÓãºÊý¾Ý¿âÖÐµÄ±í±¸·Ý³ÉÎı¾
Declare @tabname varchar(200),@ip varchar(50),@user varchar(50),@password varchar(50),@sql varchar(1000),@filetxt varchar(200),@file varchar(50)
set @tabname='database.dbo.tb' --±íÃû
set @ip='127.0.0.1' --IPµØÖ·£¬±¾»úµÄ»°¾Íд127.0.0.1
set @user='sa' ......