【SQLSERVER】存储过程基础

发布时间：2020-12-12 16:02:57 所属栏目：MsSql教程来源：网络整理

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

1.声明变量
DECLARE ? @F001 ? SMALLINT ,
??? ??? ????? @F002 ? INTEGER ,
??? ??? ????? @F003 ? VARCHAR ( 20 ),
??? ??? ????? @F004 ? CHAR ( 20 ),
??? ??? ????? @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
??????? WHEN ? [ testexpression2 ] ? THEN ?? @F002
??????? WHEN ? [ testexpression3 ] ? THEN ?? @F003
??????? WHEN ? [ testexpression4 ] ? THEN ?? @F004
END
5.循环(while)
While ?condition? Begin
??? [ ?statements? ]
End
6.动态定义游标
SET ? @strSQL ? = ? ' ?DECLARE??name_cursor??CURSOR??FOR?? ' ? + ? @inSQL
EXEC ??( @strSQL )
7.遍历游标
FETCH ? NEXT ? FROM ?name_cursor? into ? @F001 , @F002
WHILE ?? @@FETCH_STATUS ? = ? 0 ? BEGIN

??????? FETCH ? NEXT ? FROM ?name_cursor? into ? @F001 , @F002

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

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

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

创建临时表的另类方法：
select ?a.name,a.password? from
with
as ?temp1
select ? * ? from ?emp
( select ? * ? from ?temp1
union
select ? * ? from ?temp1)?a
where ?a.name = ' hao ' ;?
12.存储过程的调用及返回值
（1）存储过程的声明
CREATE ? PROCEDURE ?name_produce
???? @F001 ?? VARCHAR ( 20 ),
??????????? @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? As ?OleDb.OleDbParameter? = ?SqlCmd.Parameters.Add(?_
???????? 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）存储过程调用存储过程
DECLARE ? @C001 ??????? VARCHAR ( 20 ),
????????????? @C002 ??????? SMALLINT
EXEC ?name_produce? @C001 , @C002 ?output

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

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

转至:http://www.cnblogs.com/sekihin/archive/2007/05/28/762511.html?

（编辑：李大同）

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