SQL SERVER 存储过程基础集

1.声明变量 DECLARE @F001 SMALLINT,              @F002 INTEGER,              @F003 VARCHAR(20),              @F004 CHAR(20),              @F002 MONEY2.赋值语句 set @F001 = space(40)3.条件判断(IF...ELSE) If condition Begin    [ statements  ]ENDELSE BEGIN    [ elseifstatements ]END4.多分支判断(case...when...then...else...end）SET @F011 =CASE        WHEN [testexpression1] THEN  @F001        WHEN [testexpression2] THEN  @F002        WHEN [testexpression3] THEN  @F003        WHEN [testexpression4] THEN  @F004END5.循环(while) While condition Begin   [ statements ]End6.动态定义游标 SET @strSQL = '' DECLARE   name_cursor   CURSOR   FOR  '' + @inSQLEXEC   (@strSQL)7.遍历游标FETCH NEXT FROM name_cursor into @F001,@F002WHILE  @@FETCH_STATUS = 0 BEGIN       FETCH NEXT FROM name_cursor into @F001,@F002END说明：FETCH_STATUS检索到数据返回0，失败返回-1，可判断是否滚动未到结尾。8.获得游标行数SET @RECCNT = @@ROWCOUNT9.事务处理BEGIN distributed transactionWHILE @@TRANCOUNT > 0       commit transaction10.字符串连接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 fromwithas temp1select * from emp(select * from temp1unionselect * from temp1) awhere a.name=''hao''; 12.存储过程的调用及返回值（1）存储过程的声明 CREATE PROCEDURE name_produce    @F001  VARCHAR(20),           @F002  SMALLINT OUTPUT（2）VB.NET调用存储过程 Private SqlCmd As New OleDb.OleDbCommandSqlCmd.CommandText = "prNK3020SC03"SqlCmd.CommandType = CommandType.StoredProcedureDim 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.OutputSqlCmd.Parameters("@F001").Value = aF001SqlCmd.Parameters("@F002").Value = aF002SqlCmd.ExecuteNonQuery()aF002 = SqlCmd.Parameters("@F002").Value.ToString()（3）存储过程调用存储过程 DECLARE @C001       VARCHAR(20),              @C002       SMALLINTEXEC name_produce @C001,@C002 outputCREATE PROCEDURE dbo.getUserName@UserID int,@UserName varchar(40) outputasset nocount onbeginif @UserID is null returnselect @UserName=usernamefrom dbo.[userinfo]where userid=@UserIDreturnend13.Update语句常见错误总结--√Update name_table set        F001 = @F181,        F002 = @F182Where        F003 = @F003--×Update name_table        F001 = @F181,        F002 = @F182Where        F003 = @F003--×Update name_table set        F001 = @F181,        F002 = @F182,Where        F003 = @F003--×Update name_table set        ,F001 = @F181        ,F002 = @F182Where        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)