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

)Values(

@F001,@F002,

)

--×

INSERT?name_table(

F001,@F002

)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!