SQL SERVER 存储过程基础集（转载）

 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
)