【SQLSERVER】存储过程基础

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,FMNAM,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
)
-- ×
INSERT   INTO  name_table(
F001,F002
) Values (
@F001 , @F002 ,
)
-- ×
INSERT   name_table(
F001,F002
) Values (
@F001 , @F002
)

 

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