【网学网提醒】：网学会员,鉴于大家对SqlServer存储过程语法十分关注，会员在此为大家搜集整理了“SqlServer存储过程语法”一文，供大家参考学习!
*****************************************************
***author：Susan
***date:2005/08/05
***expliation:如何寫存儲過程的格式及例子，有游標的用法！
***本版:SQLSERVER版！
******************************************************/
在存儲過程中的格式規格：
CREATEPROCEDUREXXX
/*
列舉傳入參數
1：名稱，2：類型，包括長度
Eg:@strUNIT_CODEvarCHAR(3)
*/
參數1，
參數2……………
As
/*
定義內部參數
1：名稱，2：類型，包括長度
Eg:@strUNIT_CODEvarCHAR(3)
*/
Declare
參數1，
參數2……………
/*
初始化內部參數
Eg:SET@strUNIT_CODE=’’
*/
Set參數1的初始值
Set參數2的初始值…………
/*
過程的主內容區
Trascation：這裡起到的作用是，如果他中間的任何一個執行錯誤，就全部執行都返回，這裡sqlsever7.0以前一定要寫入，以後的就可以省略
Return：結束這支sp
*/
Begintrascation
/*
1:可以取得需要的值以存在內部參數中
Eg:SELECT@strUNIT_CODE=UNIT_CODEFROMUNITWHERE…….
2:可以用取到的或傳入的參數進行判斷，來進行update,insert,delete等等操作
eg:IF@strUNIT_CODE=’’
BEGIN
//具體的操作
End
Else
Begin
//具體的操作
End
3：有關游標的問題
Eg:
declaredbcursorfor//聲明一個游標(db為其名稱)
SELECTUNIT_NAMEFROMUNITWHERELEFT(UNIT_CODE,2)=LEFT(@strTO,2)//記錄集
opendb//打開游標
fetchnextfromdbinto@strUNIT_NAME//將第一個值放入一個參數中
while@@fetch_status=0---存在本筆值向下循環
(0：順利執行;-1：失敗，或資料列超出結果集;-2：擷取的資料列已遺漏)
BEGIN----開始循環
//個體操作
End----結束循環
Closedb---關閉游標
deallocatedb//移除資料指標參考
*/
Committrascation
Return
下面是一個例子
CREATEPROCEDURETEST_2
@strTOVARCHAR(3)
AS
DECLARE
@strUNIT_NAMEVARCHAR(800),
@strSQLVARCHAR(8000),
@LinkVARCHAR(1),
@Link1VARCHAR(1)
SET@strUNIT_NAME=''
SET@strSQL=''
SET@Link=''
SET@Link1=''
/*
處理update的部分
EXECTEST_2'011'
EXECTEST_2''
SELECTUNIT_NAMEFROMUNITWHEREUNIT_CODE='011'
*/
BEGINTRANSACTION
IF@strTO<>''
BEGIN
UPDATEUNITSETUNIT_NAME=REPLACE(UNIT_NAME,'*','')WHEREUNIT_CODE=@strTO
END
ELSE
BEGIN
UPDATEUNITSETUNIT_NAME=UNIT_NAME+'*'WHEREUNIT_CODE='011'
END
/*
EXECTEST_2'011'
功能說明：
本sp用於處理cursor問題
*/
IF@strTO<>''
BEGIN
declaredbcursorfor--必需聲明在查詢的前面
SELECTUNIT_NAMEFROMUNITWHERELEFT(UNIT_CODE,2)=LEFT(@strTO,2)---取到相關信息
END
ELSE
BEGIN
declaredbcursorfor--必需聲明在查詢的前面
SELECTUNIT_NAMEFROMUNITWHERELEFT(UNIT_CODE,2)=LEFT('011',2)---取到相關信息
END
opendb---開起取到的信息
fetchnextfromdbinto@strUNIT_NAME---把第一筆放入@strUNIT_NAME中
while@@fetch_status=0---表示存在本筆資料
BEGIN----開始循環
set@strSQL=@strSQL+@Link1+@Link+@strUNIT_NAME----設定保存的值
fetchnextfromdbinto@strUNIT_NAME----進行下次循環
SET@Link=CHAR(13)+CHAR(10)
SET@Link1=','
END----結束循環
closedb---關閉信息
deallocatedb---移除資料指標參考
SELECT@strSQL
COMMITTRANSACTION
RETURN
如果循环insert的例子
DECLARE@strLoginIDVARCHAR(16)
BEGIN
declaredbcursorfor
SELECTLoginIDFROMdbo.s_UsersWHERElen(UnitCoding)in(9,12)
END
opendb
fetchnextfromdbinto@strLoginID
while@@fetch_status=0BEGIN
insertintos_P_User
select@strLoginID,LevelIDfroms_P_UserwhereLoginID='aa'
fetchnextfromdbinto@strLoginID
END
closedb
deallocatedb
一、TRUNCATE
二、SelectINTO建表
把一个表中的数据复制到另外一个表中。
三、InsertINTOSelect
四、补充：临时表
临时表存储在系统数据库tempdb中
临时表会被系统隐式地丢弃
---------------------------------------------------------
五、存储过程(**)
一、简介：
存储过程（StoredProcedure），是一组为了完成特定功能的SQL语句，集经编译后
存储在数据库中，用户通过指定存储过程的名字并给出参数，如果该存储过程带有参数来执行
它，
在SQLServer的系列版本中，存储过程分为两类：系统提供的存储过程和用户自定义存储过程
。
系统SP，主要存储master数据库中，并以sp_为前缀并且系统存储过程主要是从系统表中获取
信息，从而为系统管理员管理SQLServer。用户自定义存储过程是由用户创建，并能完成
某一特定功能，如：查询用户所需数据信息的存储过程。
存储过程具有以下优点
1.存储过程允许标准组件式编程(模块化设计)
存储过程在被创建以后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句，而
且数
据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响。因为应用程序源代
码只包含存
储过程的调用语句，从而极大地提高了程序的可移植性。
2.存储过程能够实现快速的执行速度
如果某一操作包含大量的Transaction-SQL代码,，或分别被多次执行，那么存储过程要比批处理
的
执行速度快很多，因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进
行分析优
化，并给出最终被存在系统表中的执行计划，而批处理的Transaction-SQL语句在每次运行时
都要进行
编译和优化，因此速度相对要慢一些。
3.存储过程能够减少网络流量
对于同一个针对数据数据库对象的操作，如查询修改，如果这一操作所涉及到的Transaction-SQL
语句被组织成一存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调
用语句，否
则将是多条SQL语句从而大大增加了网络流量降低网络负载。
4.存储过程可被作为一种安全机制来充分利用
系统管理员通过，对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的
限
制。
二、变量
@I
三、流程控制语句(ifelse|selectcase|while)
Select...CASE实例
DECLARE@iRetINT,@PKDispVARCHAR(20)
SET@iRet='1'
Select@iRet=
CASE
WHEN@PKDisp='一'THEN1
WHEN@PKDisp='二'THEN2
WHEN@PKDisp='三'THEN3
WHEN@PKDisp='四'THEN4
WHEN@PKDisp='五'THEN5
ELSE100
END
四、存储过程格式
创建存储过程
CreateProcdbo.存储过程名
存储过程参数
AS
执行语句
RETURN
执行存储过程
GO
*********************************************************/
--变量的声明,sql里面声明变量时必须在变量前加@符号
DECLARE@IINT
--变量的赋值，变量赋值时变量前必须加set
SET@I=30
--声明多个变量
DECLARE@svarchar(10),@aINT
--Sql里if语句
IF条件BEGIN
执行语句
END
ELSEBEGIN
执行语句
END
DECLARE@dINT
set@d=1
IF@d=1BEGIN
--打印
PRINT'正确'
END
ELSEBEGIN
PRINT'错误'
END
--Sql里的
多条件选择语句.
DECLARE@iRetINT,@PKDispVARCHAR(20)
SET@iRet=1
Select@iRet=
CASE
WHEN@PKDisp='一'THEN1
WHEN@PKDisp='二'THEN2
WHEN@PKDisp='三'THEN3
WHEN@PKDisp='四'THEN4
WHEN@PKDisp='五'THEN5
ELSE100
END
--循环语句
WHILE条件BEGIN
执行语句
END
DECLARE@iINT
SET@i=1
WHILE@i<1000000BEGIN
set@i=@i+1
END
--打印
PRINT@i
--TRUNCATE删除表中的所有行，而不记录单个行删除操作，不能带条件
/*
TRUNCATETABLE在功能上与不带Where子句的Delete语句相同：二者均删除表中的全部行
。但TRUNCATETABLE比Delete速度快，且使用的系统和事务日志资源少。
Delete语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATETABLE通过
释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
TRUNCATETABLE删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用
的计数值重置为该列的种子。如果想保留标识计数值，请改用Delete。如果要删除表定义及其数据，请
使用DropTABLE语句。
对于由FOREIGNKEY约束引用的表，不能使用TRUNCATETABLE，而应使用不带Where子句的
Delete语句。由于TRUNCATETABLE不记录在日志中，所以它不能激活触发器。
TRUNCATETABLE不能用于参与了索引视图的表。
示例
下例删除authors表中的所有数据。*/
TRUNCATETABLEauthors
--SelectINTO从一个查询的计算结果中创建一个新表。数据并不返回给客户端，这一点和普通的
--Select不同。新表的字段具有和Select的输出字段相关联（相同）的名字和数据类型。
select*intoNewTable
fromUname
--InsertINTOSelect
--表ABC必须存在
--把表Uname里面的字段Username复制到表ABC
InsertINTOABCSelectUsernameFROMUname
--创建临时表
CreateTABLE#temp(
UIDintidentity(1,1)PRIMARYKEY,
UserNamevarchar(16),
Pwdvarchar(50),
Agesmallint,
Sexvarchar(6)
)
--打开临时表
Select*from#temp
--存储过程
--要创建存储过程的数据库
UseTest
--判断要创建的存储过程名是否存在
ifExists(SelectnameFromsysobjectsWherename='csp_AddInfo'And
type='P')
--删除存储过程
DropProceduredbo.csp_AddInfo
Go
--创建存储过程
CreateProcdbo.csp_AddInfo
--存储过程参数
@UserNamevarchar(16),
@Pwdvarchar(50),
@Agesmallint,
@Sexvarchar(6)
AS
--存储过程语句体
insertintoUname(UserName,Pwd,Age,Sex)
values(@UserName,@Pwd,@Age,@Sex)
RETURN
--执行
GO
--执行存储过程
EXECcsp_AddInfo'Junn.A','123456',20,'男'
---查表结构
SELECTTOP100PERCENT--a.id,
CASEWHENa.colorder=1THENd.nameELSE''ENDAS表名,
CASEWHENa.colorder=1THENisnull(f.value,'')ELSE''ENDAS表说明,
a.colorderAS字段序号,a.nameAS字段名,CASEWHENCOLUMNPROPERTY(a.id,
a.name,'IsIdentity')=1THEN'√'ELSE''ENDAS标识,
CASEWHENEXISTS
(SELECT1
FROMdbo.sysindexessiINNERJOIN
dbo.sysindexkeyssikONsi.id=sik.idANDsi.indid=sik.indidINNERJOIN
dbo.syscolumnsscONsc.id=sik.idANDsc.colid=sik.colidINNERJOIN
dbo.sysobjectssoONso.name=si.nameANDso.xtype='PK'
WHEREsc.id=a.idANDsc.colid=a.colid)THEN'√'ELSE''ENDAS主键,
b.nameAS类型,a.lengthAS长度,COLUMNPROPERTY(a.id,a.name,'PRECISION')
AS精度,ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0)AS小数位数,
CASEWHENa.isnullable=1THEN'√'ELSE''ENDAS允许空,ISNULL(e.text,'')
AS默认值,ISNULL(g.[value],'')AS字段说明,d.crdateAS创建时间,
CASEWHENa.colorder=1THENd.refdateELSENULLENDAS更改时间
FROMdbo.syscolumnsaLEFTOUTERJOIN
dbo.systypesbONa.xtype=b.xusertypeINNERJOIN
dbo.sysobjectsdONa.id=d.idANDd.xtype='U'AND
d.status>=0LEFTOUTERJOIN
dbo.syscommentseONa.cdefault=e.idLEFTOUTERJOIN
sys.extended_propertiesgONa.id=g.major_idANDa.colid=g.minor_idAND
g.name='MS_Description'LEFTOUTERJOIN
sys.extended_propertiesfONd.id=f.major_idANDf.minor_id=0AND
f.name='MS_Description'
ORDERBYd.name,字段序号
