SQLSERVER存储过程基础

SQLSERVER存储过程基础

1.声明变量

DECLARE     @F001  SMALLINT,  (三元素，声明declare+变量名+类型)

              @F002  INTEGER,

              @F003  VARCHAR(20),

              @F004  CHAR(20),

              @@F002  MONEY

2.赋值语句

SET  @F001 =space(40)   @F002= 3

3.条件判断(IF...ELSE)

IF  condition  BEGIN

    [statements  ]

END

ELSE  BEGIN   (ELSE IF condition2 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.动态定义游标(*)

DECLARE @strSQL  nvarchar(30)

DECLARE @inSQL  nvarchar(50)   -- select...

SET @strSQL = ' DECLARE  name_cursor CURSOR FOR  ' + @inSQL

EXEC (@strSQL)

7.遍历游标

FETCH NEXT  FROM name_cursor into@F001,@F002  （要求与游标的返回值对应）

WHILE @@FETCH_STATUS = 0 BEGIN  （如@inSQL是selectf001,f002 from tablename)

      FETCH NEXT  FROM  name_cursor into @F001,@F002

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

8.获得游标行数

SET @RECCNT = @@ROWCOUNT   --内嵌参数@@FETCH_STATUS 同

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) convert会加引号？

11.存储过程的调用及返回值

（1）存储过程的创建 (sql server数据库中create procedure)

CREATE  PROCEDURE  material_solo_days_proc

(@materialnovarchar(20),

@year_month datetime,

@totalday int,

@qty decimal(18,4)=0 output  --总量,也会return

)

AS

BEGIN

       DECLARE@@count int

       SET@@count = 0

       DECLARE@@dailyqty decimal(18,4)

       SET@@dailyqty = 0

       SET@qty = 0

       WHILE@@count < @totalday

       BEGIN  

              --计算日流水量

              EXECUTE @@dailyqty = material_solo_oneday_proc @materialno,@year_month

              SET @qty = @qty + @@dailyqty

              SET @year_month = dateadd(dd,1,@year_month)    

              SET @@count = @@count + 1

       END

       RETURN@qty

END

（2）SQL-DELPHI调用存储过程(open/execproc)

 withmaterial_account_proc do

 begin

       Close;

       ProcedureName:='material_account_proc';

       Parameters.Clear;

       Parameters.Refresh;

       Parameters.ParamByName('@year_month').Value:= year_month;

       Parameters.ParamByName('@condition').Value:= ''; //string不匹配nvarchar

       open;

       y :=Parameters.ParamByName('@count').Value;

       x := Parameters.ParamByName('@totalday').Value;

 end;

（3）存储过程调用存储过程

DECLARE @C001       VARCHAR(20),

         @C002       SMALLINT

EXEC  name_produce  @C001,@C002 output

/

12.动态sql语句的创建和执行()

    CREATE      PROCEDURE              usp_GetSalesHistory

　　(

　　       @WhereClauseNVARCHAR(2000)=NULL

　　)

　　AS

　　BEGIN

　　DECLARE     @SelectStatement  NVARCHAR(2000)

　　DECLARE     @FullStatement     NVARCHAR(4000)

　　SET        @SelectStatement = 'SELECT      *     FROM   SalesHistory'

　　SET        @FullStatement = @SelectStatement +ISNULL(@WhereClause,' ')

　　PRINT    @FullStatement

　　EXECUTE    sp_executesql         @FullStatement

　　/*EXECUTE(@FullStatement) */

END

说明: sp_executesql  执行的不能是varchar只能是nvarchar，sp_executesql 支持独立于 Transact-SQL 字符串设置参数值，有更多例子网上搜。

Execute可以是varchar,而且execute的sql语句中的参数只能是属性列或者表

13.Update语句常见错误总结 (update .,.set where)

--√

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

)