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))
declare @aSQL char(100)
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)