【SQLSERVER】存储过程基础

发布时间：2020-12-12 14:10:51 所属栏目：MsSql教程来源：网络整理

导读：1.声明变量? DECLARE ? @F001 ? SMALLINT , ???? ??? ????? @F002 ? INTEGER ,0)">??? ????? @F003 ? VARCHAR ( 20 ), ??? ??? ?????? @F004 ? CHAR ( ??? ????? @F002 ? MONEY 2.赋值语句? set ? @F001 ? = ? space ( 40 ) 3.条件判断(IF...ELSE)? If ?cond

1.声明变量?
DECLARE? @F001? SMALLINT,
???? ??? ????? @F002? INTEGER,0)">??? ????? @F003? VARCHAR( 20),
??? ??? ?????? @F004? CHAR( ??? ????? @F002? MONEY
2.赋值语句?
set? @F001? =? space( 40)
3.条件判断(IF...ELSE)?
If?condition? Begin
???? [ ?statements?? ]
END
ELSE? BEGIN
???? ?elseifstatements? END
4.多分支判断(case...when...then...else...end）
SET? @F011? =
CASE
???????? WHEN? testexpression1 ]? THEN?? @F001
???????? testexpression2 @F002
???????? testexpression3 @F003
???????? testexpression4 @F004
5.循环(while) ?
While?condition? Begin
??? ?statements? End
6.动态定义游标?
@strSQL? ' ?DECLARE??name_cursor??CURSOR??FOR?? '? +? @inSQL
EXEC??( @strSQL)
7.遍历游标
FETCH? NEXT? FROM?name_cursor? into? @F001,@F002
WHILE?? @@FETCH_STATUS? =? 0? BEGIN

??????? @F002

END 说明：FETCH_STATUS检索到数据返回0，失败返回-1，可判断是否滚动未到结尾。

8.获得游标行数
@RECCNT? =? @@ROWCOUNT
9.事务处理
BEGIN? distributed? transaction

WHILE? @@TRANCOUNT? >? 0
??????? commit? transaction
10.字符串连接
@m_sql? +? ?Where?F001?=? '''? ''''
?F002?=? CONVERT( varchar,0)">@F002)
11.创建临时表存储外部数据表
说明:临时过程用 # 和 ## 命名，可以由任何用户创建。创建过程后，局部过程的所有者是唯一可以使用该过程的用户。
CREATE? TABLE?#DMPARHED
(FMCD??? int,
FMNAM?? varchar( 50),
MGYO1?? smallint,
constraint?DMPARHED_P? primary? key?(FMCD))
@aSQL? ''
INSERT?INTO?#DMPARHED '
?SELECT?FMCD,FMNAM,MGYO1?FROM ?OPENQUERY(Lk_MDB_NEO32,? '' SELECT?FMCD,MGYO1?FROM?DMPARHED ?WHERE?SYSNO?=?1 ) execute( @aSQL)

创建临时表的另类方法：?
select?a.name,a.password? from
with
as?temp1
select? *? from?emp
( from?temp1
union
from?temp1)?a
where?a.name = hao ';?
12.存储过程的调用及返回值
（1）存储过程的声明?
PROCEDURE?name_produce
???? @F001?? VARCHAR( @F002?? SMALLINT?OUTPUT （2）VB.NET调用存储过程?
Private?SqlCmd? As? New?OleDb.OleDbCommand

SqlCmd.CommandText?=?"prNK3020SC03"
SqlCmd.CommandType?=?CommandType.StoredProcedure

Dim?parampre1? As?OleDb.OleDbParameter?=?SqlCmd.Parameters.Add(?_
????????? New?OleDb.OleDbParameter("@F001",?OleDb.OleDbType.VarChar,?20,?_
?????????ParameterDirection.Input))
Dim?parampre2? New?OleDb.OleDbParameter("@F002",?OleDb.OleDbType.SmallInt))
parampre2.Direction?=?ParameterDirection.Output

SqlCmd.Parameters("@F001").Value?=?aF001
SqlCmd.Parameters("@F002").Value?=?aF002
SqlCmd.ExecuteNonQuery()
aF002?=?SqlCmd.Parameters("@F002").Value.ToString()
（3）存储过程调用存储过程?
@C001??????? VARCHAR( @C002??????? SMALLINT
EXEC?name_produce? @C001,0)">@C002?output

PROCEDURE?dbo.getUserName
@UserID? int,0)">@UserName? varchar( 40)?output
as
set?nocount? on
begin
if? @UserID? is? null? return
select? @UserName =username
from?dbo. userinfo where?userid = @UserID
end

13.Update语句常见错误总结?
-- √
Update?name_table? set
???????F001? @F181,
???????F002? @F182
Where
???????F003? @F003
× Update?name_table
???????F001? @F182,255)">set
???????,F001? @F181
???????,F002? @F003
14.Insert语句常见语法错误总结?
INSERT? INTO?name_table(
,KEY_FIELD，BUSYOCD
) Values(
@F002
)
INTO?name_table(
F001,F002
) Values(
,0)">@F002,
)
INSERT??name_table(
F001,0)">@F002 )

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!