MSSQL之二十一 存儲過程案例

Stored Procedure（存儲過程）編寫經驗和優化措施

一、前言：在經過一段時間的存儲過程開發之后，寫下了一些開發時候的小結和經驗與大家共享，希望對大家有益，主要是針對Sybase和SQL Server數據庫，但其它數據庫應該有一些共性。

二、適合讀者對象：數據庫開發程序員，數據庫的數據量很多，涉及到對SP（存儲過程）的優化的項目開發人員，對數據庫有濃厚興趣的人。

三、介紹：在數據庫的開發過程中，經常會遇到復雜的業務邏輯和對數據庫的操作，這個時候就會用SP來封裝數據庫操作。如果項目的SP較多，書寫又沒有一定的規范，將會影響以后的系統維護困難和大SP邏輯的難以理解，另外如果數據庫的數據量大或者項目對SP的性能要求很，就會遇到優化的問題，否則速度有可能很慢，經過親身經驗，一個經過優化過的SP要比一個性能差的SP的效率甚至高幾百倍。

四、內容：

1、開發人員如果用到其他庫的Table或View，務必在當前庫中建立View來實現跨庫操作，最好不要直接使用“databse.dbo.table_name”，因為sp_depends不能顯示出該SP所使用的跨庫table或view，不方便校驗。

2、開發人員在提交SP前，必須已經使用set showplan on分析過查詢計劃，做過自身的查詢優化檢查。

3、高程序運行效率，優化應用程序，在SP編寫過程中應該注意以下幾點：

a) ? ? ? ?SQL的使用規范：

i. ?盡量避免大事務操作，慎用holdlock子句，提高系統并發能力。

ii. 盡量避免反復訪問同一張或幾張表，尤其是數據量較大的表，可以考慮先根據條件提取數據到臨時表中，然后再做連接。

iii.盡量避免使用游標，因為游標的效率較差，如果游標操作的數據超過1萬行，那么就應該改寫；如果使用了游標，就要盡量避免在游標循環中再進行表連接的操作。

iv. 注意where字句寫法，必須考慮語句順序，應該根據索引順序、范圍大小來確定條件子句的前后順序，盡可能的讓字段順序與索引順序相一致，范圍從大到小。

v. ?不要在where子句中的“=”左邊進行函數、算術運算或其他表達式運算，否則系統將可能無法正確使用索引。

vi. 盡量使用exists代替select count(1)來判斷是否存在記錄，count函數只有在統計表中所有行數時使用，而且count(1)比count(*)更有效率。

vii.盡量使用“>=”，不要使用“>”。

viii.注意一些or子句和union子句之間的替換

ix.注意表之間連接的數據類型，避免不同類型數據之間的連接。

x. 注意存儲過程中參數和數據類型的關系。

xi.注意insert、update操作的數據量，防止與其他應用沖突。如果數據量超過200個數據頁面（400k），那么系統將會進行鎖升級，頁級鎖會升級成表級鎖。

b) 索引的使用規范：

i. ?索引的創建要與應用結合考慮，建議大的OLTP表不要超過6個索引。

ii. 盡可能的使用索引字段作為查詢條件，尤其是聚簇索引，必要時可以通過index index_name來強制指定索引

iii.避免對大表查詢時進行table scan，必要時考慮新建索引。

iv. 在使用索引字段作為條件時，如果該索引是聯合索引，那么必須使用到該索引中的第一個字段作為條件時才能保證系統使用該索引，否則該索引將不會被使用。

v. ?要注意索引的維護，周期性重建索引，重新編譯存儲過程。

c)tempdb的使用規范：

i. ?盡量避免使用distinct、order by、group by、having、join、cumpute，因為這些語句會加重tempdb的負擔。

ii. 避免頻繁創建和刪除臨時表，減少系統表資源的消耗。

iii.在新建臨時表時，如果一次性插入數據量很大，那么可以使用select into代替create table，避免log，提高速度；如果數據量不大，為了緩和系統表的資源，建議先create table，然后insert。

iv. 如果臨時表的數據量較大，需要建立索引，那么應該將創建臨時表和建立索引的過程放在單獨一個子存儲過程中，這樣才能保證系統能夠很好的使用到該臨時表的索引。

v. ?如果使用到了臨時表，在存儲過程的最后務必將所有的臨時表顯式刪除，先truncate table，然后drop table，這樣可以避免系統表的較長時間鎖定。

vi. 慎用大的臨時表與其他大表的連接查詢和修改，減低系統表負擔，因為這種操作會在一條語句中多次使用tempdb的系統表。

d)合理的算法使用：

根據上面已提到的SQL優化技術和ASE Tuning手冊中的SQL優化內容,結合實際應用,采用多種算法進行比較,以獲得消耗資源最少、效率最高的方法。具體可用ASE調優命令：set statistics io on, set statistics time on , set showplan on 等。

===============================

金額阿拉伯數字轉換為中文的自定義函數

CREATE FUNCTION ChangeBigSmall

(@ChangeMoney money)

RETURNS VarChar(100) AS

BEGIN

Declare ? ?@String1 ? ?char(20)

Declare ? ?@String2 ? ?char(30)

Declare ? ?@String4 ? ?Varchar(100)

Declare @String3 ? ? Varchar(100) ? ?--從原A值中取出的值

Declare @i ? ? ? ? int ? ? ? ?--循環變量

Declare @J ? ? ? ? Int ? ? ? ?--A的值乘以100的字符串長度

Declare @Ch1 ? ? ? ? Varchar(100) ? ?--數字的漢語讀法

Declare @Ch2 ? ? ? ? Varchar(100) ? ?--數字位的漢字讀法

Declare @Zero ? ? ? ? Int ? ? ? ?--用來計算連續有幾個零

Declare ? ?@Returnvalue ? ?VarChar(100)

Select ?@Returnvalue = ''

Select ? ? @String1 = '零壹貳叁肆伍陸柒捌玖'

Select ? ?@String2 = '萬仟佰拾億仟佰拾萬仟佰拾元角分'

Select @String4 = Cast(@ChangeMoney*100 as int)

select @J=len(cast((@ChangeMoney*100) as int))

Select @String2=Right(@String2,@J)

Select ? ?@i = 1

while ? ?@i<= @j Begin

Select @String3 = Substring(@String4,@i,1)

if @String3<>'0' Begin

Select ? ? @Ch1 = Substring(@String1, Cast(@String3 as Int) + 1, 1)

Select ? ?@Ch2 = Substring(@String2, @i, 1)

Select ? ?@Zero = 0 ? ? ? ? ? ? ? ? ? ?--表示本位不為零

end

else Begin

If (@Zero = 0) Or (@i = @J - 9) Or (@i = @J - 5) Or (@i = @J - 1)

Select @Ch1 = '零'

Else

Select @Ch1 = ''

Select @Zero = @Zero + 1 ? ? ? ? ? ? --表示本位為0

--如果轉換的數值需要擴大，那么需改動以下表達式 I 的值。

Select @Ch2 = ''

If @i = @J - 10 ?Begin

Select @Ch2 = '億'

Select @Zero = 0

end

If @i = @J - 6 Begin

Select @Ch2 = '萬'

Select @Zero = 0

end

if @i = @J - 2 Begin

Select @Ch2 = '元'

Select @Zero = 0

end

If @i = @J

Select @Ch2 = '整'

end

Select @Returnvalue = @Returnvalue + @Ch1 + @Ch2

select @i = @i+1

end

--最后將多余的零去掉

If CharIndex('仟仟',@Returnvalue) <> 0

Select @Returnvalue = Replace(@Returnvalue, '仟仟', '仟')

If CharIndex('佰佰',@Returnvalue) <> 0

Select @Returnvalue = Replace(@Returnvalue, '佰佰', '佰')

If CharIndex('零元',@Returnvalue) <> 0

Select @Returnvalue = Replace(@Returnvalue, '零元', '元')

If CharIndex('零萬',@Returnvalue) <> 0

Select @Returnvalue = Replace(@Returnvalue, '零萬', '萬')

If CharIndex('零億',@Returnvalue) <> 0

Select @Returnvalue = Replace(@Returnvalue, '零億', '億')

If CharIndex('零整',@Returnvalue) <> 0

Select @Returnvalue = Replace(@Returnvalue, '零整', '整')

If CharIndex('零佰',@Returnvalue) <> 0

Select @Returnvalue = Replace(@Returnvalue, '零佰', '零')

If CharIndex('零仟',@Returnvalue) <> 0

Select @Returnvalue = Replace(@Returnvalue, '零仟', '零')

If CharIndex('元元',@Returnvalue) <> 0

Select @Returnvalue = Replace(@Returnvalue, '元元', '元')

return @Returnvalue

END

===========================================

MS SQL SERVER 圖像或大文本的輸入輸出

在MS SQL SERVER 安裝目錄下有個可執行文件叫 TEXTCOPY.EXE

可對 MS SQL SERVER 中的文本或圖像數據進行輸入輸出.

不過你可以在MS-DOS方式下執行textcopy /? 得到它的描述。

下面是這個工具的描述:

Copies a single text or image value into or out of SQL Server. The val

ue

is a specified text or image 'column' of a single row (specified by th

e

"where clause") of the specified 'table'.

If the direction is IN (/I) then the data from the specified 'file' is

copied into SQL Server, replacing the existing text or image value. If

the

direction is OUT (/O) then the text or image value is copied from

SQL Server into the specified 'file', replacing any existing file.

TEXTCOPY [/S ][sqlserver]] [/U [login]] [/P ][password]]

[/D ][database]] [/T table] [/C column] [/W"where clause"]

[/F file] [{/I | /O}] [/K chunksize] [/Z] [/?]

/S sqlserver ? ? ? The SQL Server to connect to. If 'sqlserver' is n

ot

specified, the local SQL Server is used.

/U login ? ? ? ? ? The login to connect with. If 'login' is not spec

ified,

a trusted connection will be used.

/P password ? ? ? ?The password for 'login'. If 'password' is not

specified, a NULL password will be used.

/D database ? ? ? ?The database that contains the table with the tex

t or

image data. If 'database' is not specified, the d

efault

database of 'login' is used.

/T table ? ? ? ? ? The table that contains the text or image value.

/C column ? ? ? ? ?The text or image column of 'table'.

/W "where clause" ?A complete where clause (including the WHERE keyw

ord)

that specifies a single row of 'table'.

/F file ? ? ? ? ? ?The file name.

/I ? ? ? ? ? ? ? ? Copy text or image value into SQL Server from 'fi

le'.

/O ? ? ? ? ? ? ? ? Copy text or image value out of SQL Server into '

file'.

/K chunksize ? ? ? Size of the data transfer buffer in bytes. Minimu

m

value is 1024 bytes, default value is 4096 bytes.

/Z ? ? ? ? ? ? ? ? Display debug information while running.

/? ? ? ? ? ? ? ? ? Display this usage information and exit.

You will be prompted for any required options you did not specify.

為此, 可寫一個存儲過程,調用這個命令

CREATE PROCEDURE sp_textcopy (

@srvname ? ? varchar (30),

@login ? ? ? varchar (30),

@password ? ?varchar (30),

@dbname ? ? ?varchar (30),

@tbname ? ? ?varchar (30),

@colname ? ? varchar (30),

@filename ? ?varchar (30),

@whereclause varchar (40),

@direction ? char(1))

AS

DECLARE @exec_str varchar (255)

SELECT @exec_str =

'textcopy /S ' + @srvname +

' /U ' + @login +

' /P ' + @password +

' /D ' + @dbname +

' /T ' + @tbname +

' /C ' + @colname +

' /W "' + @whereclause +

'" /F ' + @filename +

' /' + @direction

EXEC master..xp_cmdshell @exec_str

下面是一個拷貝圖像到SQL Server的pubs數據庫的例子, 表名pub_info, 字段名

logo，圖像文件名picture.bmp，保存到pub_id='0736'記錄 sp_textcopy @srvn

ame = 'ServerName',

@login = 'Login',

@password = 'Password',

@dbname = 'pubs',

@tbname = 'pub_info',

@colname = 'logo',

@filename = 'c:\picture.bmp',

@whereclause = " WHERE pub_id='0736' ",

@direction = 'I'

select 聯系人,訂購日期,訂購數量,產品名稱

from 客戶信息表 a,訂單信息表 b,產品信息表 c

where a.客戶編號=b.客戶編號 and b.產品編號=c.產品編號

select 聯系人,訂購日期,訂購數量,產品名稱

from 客戶信息表 a inner join 訂單信息表 b

on a.客戶編號=b.客戶編號 inner join 產品信息表 c

on b.產品編號=c.產品編號

---------------------------------------------

use 范例

go

--創建存儲過程

create procedure Query

as

select 聯系人,訂購日期,訂購數量,產品名稱

from 客戶信息表 a,訂單信息表 b,產品信息表 c

where a.客戶編號=b.客戶編號 and b.產品編號=c.產品編號

--執行存儲過程 sp_recompile作用：對自己定義的存儲過程進行重新編譯

exec sp_recompile Query

exec Query

--刪除存儲過程

drop procedure Query

select * from master.dbo.syscomments

--查看存儲過程源代碼

exec sp_helptext sp_renamedb

--修改存儲過程

alter procedure Query

with encryption

as

select 聯系人,訂購日期,訂購數量,產品名稱

from 客戶信息表 a,訂單信息表 b,產品信息表 c

where a.客戶編號=b.客戶編號 and b.產品編號=c.產品編號

--====================================================================

-- 輸出參數

create proc QueryName

@ProductID char(10), --輸入參數

@ProductName char(10) output -- 輸出參數

as

select @ProductName=產品名稱 from 產品信息表

where 產品編號 = @ProductID

--return @ProductName

go

select * from 產品信息表

--執行

-- declare @prodName varchar(25)

-- exec QueryName 'P003',@ProductName=@prodName OUTPUT

--

-- print @prodName

declare @prodName varchar(25)

exec QueryName 'P003' ,@prodName output

print @prodName

--=====================================

create proc abc

as

begin tran

print 'fs'

if @@error > 0 --or @@rowcount <> 1

goto seed

print 'fs'

seed:

if @@error > 0 --or @@rowcount <> 1

begin

--select @@error

--select @@rowcount as ssss

rollback tran

print '發生錯誤提交無法完成！！！！'

end

else

begin

commit tran

print '無錯誤發生提交正常！！！'

end

go

--==============================================================

exec abc

drop proc abc

/*

存儲過程:

將常用的或很復雜的工作，預先以sql程序寫好，

然后指定一個程序名稱保存起來，那么以后只要使用execute指令

來執行這個程序，即可自動完成該項工作。

優點：

1.存儲過程是預先編譯的，執行效率高。

2.存儲過程的執行是在服務器端，這樣就可極大地降低網絡流量。

3.重復使用。

4.如果所有客戶端都通過存儲過程來進行訪問數據，這

樣可禁止對物理表的直接訪問，提高數據安全性。

存儲過程的種類：

1.系統存儲過程。 以sp_開頭

2.擴展存儲過程。 以xp_開頭

3.用戶定義存儲過程。

*/

use master

exec xp_cmdshell 'dir C:'

-------------------------------------------------------------------------------------------------------------------------------

create table 標標公司

(

產品名稱 varchar(50) not null,

價格 smallmoney not null

)

create table 旗旗公司

(

產品名稱 varchar(50) not null,

價格 smallmoney not null

)

insert 標標公司 (產品名稱,價格) values ('原子筆',10)

insert 標標公司 (產品名稱,價格) values ('鋼筆',90)

insert 標標公司 (產品名稱,價格) values ('熒光筆',40)

insert 標標公司 (產品名稱,價格) values ('水彩筆',60)

insert 旗旗公司 (產品名稱,價格) values ('原子筆',10)

insert 旗旗公司 (產品名稱,價格) values ('鋼筆',100)

insert 旗旗公司 (產品名稱,價格) values ('2B鉛筆',20)

insert 旗旗公司 (產品名稱,價格) values ('毛鉛筆',50)

select * from 標標公司

select * from 旗旗公司

truncate table 標標公司

-----------------------------------------建立存儲過程----------------------------------------------------

alter procedure myproc1

with encryption --encryption對存儲過程進行加密

as

if 1<2

print 'AAAAA'

select * from 標標公司

where 價格 > 15

go

------------------

exec myproc1

-----------------------------------------建立視圖--------------------------------------------------------

alter view myview1

with encryption

as

select * from 標標公司

where 價格 > 15

go

---------------------------

select * from myview1

------------------------建立存儲過程并設置參數------------------------------

create procedure myproc2

@param1 char(10)=null,

@param2 money=NULL

--with encryption

as

insert 標標公司 (產品名稱,價格) values (@param1,@param2)

go

--================================================================

exec myproc2 '鉛筆盒',120　　　　-- 執行存儲過程，并給定參數

go

--=============================================================

drop procedure myproc2 -- 刪除存儲過程

select * from 標標公司

-------------------------------------------

-------------------------------------------

create proc myproc3

@param1 char(10) =null,

@param2 money = null,

@param3 money output

as

insert 標標公司 (產品名稱,價格) values (@param1,@param2)

select @param3 = sum(價格)

from 標標公司

where 價格 > 15

go

-------------------------------------------------------

------------------------------------

-- 執行myproc3存儲過程，并將返回值指定給@sum.

declare @sum money

exec myproc3 '金筆',1200,@sum output

print '目前的總價格為: ' + convert(varchar,@sum)

select * from 標標公司

-------------------------------------

-------------------------------------建立群組存儲過程-------------------------

create proc myproc5;1

as

select * from 旗旗公司

go

create proc myproc5;2

as

select * from 標標公司

go

exec myproc5;1

exec myproc5;2

drop proc myproc5

--------------------------------------------修改存儲過程-------------------------------------------------

alter procedure myproc2

@param1 char(10),

@param2 money

with encryption

as

insert 標標公司 (產品名稱,價格) values (@param1,@param2)

go

-----------------------------------------------------------------------------------------------------

drop proc myproc

create proc myproc

as

declare @v int

select @v=5

while @v>0

begin

select @v*@v

select @v=@v-1

if @v=2

begin

raiserror('計數器已小于3',16,5)

--break

return

end

end

print 'wwwwwwwwwwwwwwwww'

exec myproc

這個存儲過程執行的速度還不錯.500W速度分頁只要2秒,任何頁數

可是有點問題就是,如果排序不是主鍵,ID,那么分頁就會出錯,不知道各位有什么好個意見,修改一下

-------------------------------------------------------

create procedure SqlDataPaging

@tbName varchar(255), --表名

@tbFields varchar(1000), --返回字段

@OrderField varchar(255), --排序的字段名

@PageSize int, --頁尺寸

@PageIndex int, --頁碼

@OrderType bit, --排序類型,1是升序,0是降序

@strWhere varchar(1500), --查詢條件

@Total int output --返回總記錄數

as

declare @strSql varchar(5000) --主語句

declare @strOrder varchar(200) --排序

declare @strSqlCount nvarchar(500)--查詢記錄總數主語句

declare @strtemp varchar(50) --排序附加語句

--------------排序-----------------1是降序,0未升序

if @OrderType !=0

begin

set @strtemp= '>(select max('

set @strOrder='order by ' + @OrderField + ' asc '

end

else

begin

set @strtemp= '<(select min('

set @strOrder='order by '+ @OrderField + ' desc'

end

--------------總記錄數---------------

if @strWhere !=''

begin

set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName + 'where '+ @strWhere

end

else

begin

set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName

end

--------------如果是第一頁------------

if @PageIndex =1

begin

if @strWhere !=''

begin

set @strSql = 'Select top' +str(@PageSize)+ ' ' + @tbFields + ' from '+ @tbName +' where ' + @strWhere + ' ' + @strOrder

end

else

begin

set @strSql = 'Select top' +str(@PageSize)+ ' ' + @tbFields + ' from '+ @tbName + ' ' + @strOrder

end

end

else

begin

if @strWhere !=''

begin

set @strSql='Select top ' +str(@PageSize) + ' ' + @tbFields

+' from ' + @tbName + ' where '+@OrderField +' '

+ @strtemp+ ' ' +@OrderField+ ') from (select top'

+str((@PageIndex-1)*@PageSize) + ' '+@OrderField +' from '+@tbName

+ @strWhere+ ' '+ @strOrder +') as tb)'+ @strOrder

end

else

begin

set @strSql='Select top ' + str(@PageSize) + ' ' + @tbFields

+' from ' + @tbName + ' where '+@OrderField + ' '

+ @strtemp+ ' ' +@OrderField+ ') from (select top'

+str((@PageIndex-1)*@PageSize) + ' '+@OrderField +' from '+@tbName

+ ' '+ @strOrder +') as tb)'+ @strOrder

end

end

exec sp_executesql @strSqlCount,N'@TotalCout int output',@Total output

exec(@strSql)

-----------------------------------

CREATE PROC sp_PageView

@tbname sysname, --要分頁顯示的表名

@FieldKey nvarchar(1000), --用于定位記錄的主鍵(惟一鍵)字段,可以是逗號分隔的多個字段

@PageCurrent int=1, --要顯示的頁碼

@PageSize int=10, --每頁的大小(記錄數)

@FieldShow nvarchar(1000)='', --以逗號分隔的要顯示的字段列表,如果不指定,則顯示所有字段

@FieldOrder nvarchar(1000)='', --以逗號分隔的排序字段列表,可以指定在字段后面指定DESC/ASC用于指定排序順序

@Where nvarchar(1000)='', --查詢條件

@PageCount int OUTPUT --總頁數

AS

SET NOCOUNT ON

--檢查對象是否有效

IF OBJECT_ID(@tbname) IS NULL

BEGIN

RAISERROR(N'對象"%s"不存在',1,16,@tbname)

RETURN

END

IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0

AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0

AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0

BEGIN

RAISERROR(N'"%s"不是表、視圖或者表值函數',1,16,@tbname)

RETURN

END

--分頁字段檢查

IF ISNULL(@FieldKey,N'')=''

BEGIN

RAISERROR(N'分頁處理需要主鍵（或者惟一鍵）',1,16)

RETURN

END

--其他參數檢查及規范

IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1

IF ISNULL(@PageSize,0)<1 SET @PageSize=10

IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'

IF ISNULL(@FieldOrder,N'')=N''

SET @FieldOrder=N''

ELSE

SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)

IF ISNULL(@Where,N'')=N''

SET @Where=N''

ELSE

SET @Where=N'WHERE ('+@Where+N')'

--如果@PageCount為NULL值,則計算總頁數(這樣設計可以只在第一次計算總頁數,以后調用時,把總頁數傳回給存儲過程,避免再次計算總頁數,對于不想計算總頁數的處理而言,可以給@PageCount賦值)

IF @PageCount IS NULL

BEGIN

DECLARE @sql nvarchar(4000)

SET @sql=N'SELECT @PageCount=COUNT(*)'

+N' FROM '+@tbname

+N' '+@Where

EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT

SET @PageCount=(@PageCount+@PageSize-1)/@PageSize

END

--計算分頁顯示的TOPN值

DECLARE @TopN varchar(20),@TopN1 varchar(20)

SELECT @TopN=@PageSize,

@TopN1=@PageCurrent*@PageSize

--第一頁直接顯示

IF @PageCurrent=1

EXEC(N'SELECT TOP '+@TopN

+N' '+@FieldShow

+N' FROM '+@tbname

+N' '+@Where

+N' '+@FieldOrder)

ELSE

BEGIN

--生成主鍵(惟一鍵)處理條件

DECLARE @Where1 nvarchar(4000),@s nvarchar(1000)

SELECT @Where1=N'',@s=@FieldKey

WHILE CHARINDEX(N',',@s)>0

SELECT @s=STUFF(@s,1,CHARINDEX(N',',@s),N''),

@Where1=@Where1

+N' AND a.'+LEFT(@s,CHARINDEX(N',',@s)-1)

+N'='+LEFT(@s,CHARINDEX(N',',@s)-1)

SELECT @Where1=STUFF(@Where1+N' AND a.'+@s+N'='+@s,1,5,N''),

@TopN=@TopN1-@PageSize

--執行查詢

EXEC(N'SET ROWCOUNT '+@TopN1

+N' SELECT '+@FieldKey

+N' INTO # FROM '+@tbname

+N' '+@Where

+N' '+@FieldOrder

+N' SET ROWCOUNT '+@TopN

+N' DELETE FROM #'

+N' SELECT '+@FieldShow

+N' FROM '+@tbname

+N' a WHERE EXISTS(SELECT * FROM # WHERE '+@Where1

+N') '+@FieldOrder)

END

-------------------------------------------

/*函數名稱: GetRecordFromPage

函數功能: 獲取指定頁的數據

參數說明: @tblName 包含數據的表名

@fldName 關鍵字段名

@PageSize 每頁記錄數

@PageIndex 要獲取的頁碼

@OrderType 排序類型, 0 - 升序, 1 - 降序

@strWhere 查詢條件(注意: 不要加where)

創建時間: 2004-07-04

修改時間: 2008-02-13

*/

Alter PROCEDURE [dbo].[GetRecordFromPage]

@tblName varchar(255), -- 表名

@fldName varchar(255), -- 字段名

@PageSize int = 10, -- 頁尺寸

@PageIndex int = 1, -- 頁碼

@OrderType bit = 0, -- 設置排序類型, 非0 值則降序

@IsCount bit = 0, -- 返回記錄總數, 非0 值則返回

@strWhere varchar(2000) = '' -- 查詢條件(注意: 不要加where)

AS

declare @strSQL varchar(6000) -- 主語句

declare @strTmp varchar(1000) -- 臨時變量

declare @strOrder varchar(500) -- 排序類型

if @OrderType != 0

begin

set @strTmp = '<(select min'

set @strOrder = ' order by [' + @fldName + '] desc'

end

else

begin

set @strTmp = '>(select max'

set @strOrder = ' order by [' + @fldName +'] asc'

end

set @strSQL = 'select top ' + str(@PageSize) + ' * from ['

+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['

+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['

+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'

+ @strOrder

if @strWhere != ''

set @strSQL = 'select top ' + str(@PageSize) + ' * from ['

+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['

+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['

+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '

+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder

if @PageIndex = 1

begin

set @strTmp = ''

if @strWhere != ''

set @strTmp = ' where (' + @strWhere + ')'

set @strSQL = 'select top ' + str(@PageSize) + ' * from ['

+ @tblName + ']' + @strTmp + ' ' + @strOrder

end

if @IsCount != 0

set @strSQL = 'select count(' + @fldName + ') as Total from [' + @tblName + ']'

exec (@strSQL)

go

-----------------------------

MySQL 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。