【SQLSERVER】 存储过程
来源:互联网 发布:g96恒线速度编程实例 编辑:程序博客网 时间:2024/06/05 01:14
ALTER PROC SP_SinoAirNumberGet@ShipmentNumber NVARCHAR(50)ASBEGINDECLARE @ColoaderShipmentNumber NVARCHAR(50),@ShipmentCode NVARCHAR(50),@errorMessage NVARCHAR(200)SET @ColoaderShipmentNumber = '' --check agent shipmentSELECT @ShipmentCode = ShipmentCode FROM Shipment s with (nolock)INNER JOIN Customer c ON s.CustomerCode = c.CustomerCode AND FranchiseCodeRelate IS NULL WHERE ShipmentNumber = @ShipmentNumber--check gateway shipmentIF @ShipmentCode IS NULL OR @ShipmentCode = ''BEGINSELECT @ShipmentCode = ShipmentCode FROM Shipment s with (nolock)INNER JOIN Customer c ON s.CustomerCode = c.CustomerCode AND FranchiseCodeRelate IS NOT NULL WHERE ShipmentNumber = @ShipmentNumberENDIF @ShipmentCode IS NULL OR @ShipmentCode = ''BEGINRAISERROR('错误的提单号码!',16,1)RETURNENDSELECT @ColoaderShipmentNumber = SinoAirNumber FROM SinoAirNumber with (nolock) WHERE ShipmentCode = @ShipmentCodeIF @ColoaderShipmentNumber IS NOT NULL AND @ColoaderShipmentNumber != ''BEGINSELECT @ColoaderShipmentNumber AS SinoAirNumberRETURNENDSET @ColoaderShipmentNumber = ''IF @ColoaderShipmentNumber IS NULL OR @ColoaderShipmentNumber = ''BEGINSELECT Top 1 @ColoaderShipmentNumber = SinoAirNumberFROM SinoAirNumber with (nolock)WHERE SinoAirNumberStatus != 'Y' OR SinoAirNumberStatus IS NULLORDER BY SinoAirNumber ASCEND IF (EXISTS (SELECT 1 FROM SinoAirNumber WHERE SinoAirNumber = @ColoaderShipmentNumber AND (SinoAirNumberStatus != 'Y' OR SinoAirNumberStatus IS NULL)))OR (EXISTS (SELECT 1 FROM SinoAirNumber WHERE SinoAirNumber = @ShipmentNumber AND SinoAirNumberStatus = 'Y' ))BEGINBEGIN TRANSACTIONBEGIN TRYUPDATE SinoAirNumber SET SinoAirNumberStatus = 'Y',ShipmentCode = @ShipmentCode WHERE SinoAirNumber = @ColoaderShipmentNumberUPDATE Shipment SET ShipmentNumber = @ColoaderShipmentNumber,ColoaderNumber=@ShipmentNumber WHERE ShipmentCode = @ShipmentCodeINSERT INTO ShipmentExtraInfo (ShipmentCode,ShipmentNumber) VALUES (@ShipmentCode,@ColoaderShipmentNumber)COMMITEND TRYBEGIN CATCHROLLBACKSET @errorMessage = ERROR_MESSAGE()RAISERROR(@errorMessage,16,1)END CATCHENDELSEBEGINSELECT Top 1 @ColoaderShipmentNumber = SinoAirNumberFROM SinoAirNumber with (nolock)WHERE SinoAirNumberStatus != 'Y' OR SinoAirNumberStatus IS NULLORDER BY SinoAirNumber ASCENDIF @ColoaderShipmentNumber IS NULL OR @ColoaderShipmentNumber = ''BEGINRAISERROR('Number pool has empty',16,1)RETURNENDSELECT @ColoaderShipmentNumber AS SinoAirNumberEND
(1).创建存储过程的语句:
CREATE PROC SP_TestName
@Parameter Type(length)
AS
BEGIN
...
END
(2).x修改存储过程:
ALTER....
(3).DECLARE关键字,声明参数(全局变量)
(4).SET语句,为参数赋值,或初始化参数
(5).SELECT @ShipmentCode = ShipmentCode FROM Shipment s with (nolock)
INNER JOIN Customer c ON s.CustomerCode = c.CustomerCode AND FranchiseCodeRelate IS NULL
WHERE ShipmentNumber = @ShipmentNumber
INNER JOIN : 内连接
with(NOLOCK):指定允许脏读。在sqlserver中,当一个事物访问某张表时,该表出于lock状态,另一事物必须等待该事物结束之后,才能访问当前表。脏读的意思是:
允许当前一事物在另一事物访问某表时,读取当前表的数据,注意只允许读取,不允许其他数据库操作。这样会造成一定数据上的错误。适用于报表。
(6).EXISTS:判断当前查询是否存在一列或多于一列,返回true或false。当当前查询无列即无数据返回时,EXISTS返回false,当数据返回时,EXISTS返回true。
(7).事物(TRANSACTION):当存在两条或两条以上的增删改(注意查询不算)语句时,应开启事务,若发生错误,rellback,并使用try catch将错误信息返回。
格式:
BEGIN TRANSACTION
BEGIN TRY
....
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
SET @errorMessage = ERROR_MESSAGE()
RAISERROR(@errorMessage,16,1) errorMessage:需要在前面变量中声明。
END CATCH
(8).RAISERROR:错误处理
RAISERROR(“errorMessage”,16,1) 16:代表错误级别 1:不知道0.0
(9).RETURN: 结束存储过程。
- 分页 SQLServer存储过程
- 分页 SQLServer存储过程
- SqlServer分页存储过程
- sqlserver存储过程
- 分页 SQLServer存储过程
- SQLserver 存储过程入门
- 分页 SQLServer存储过程
- sqlserver 存储过程 语法
- 【SQLSERVER】存储过程基础
- SQLServer 分页存储过程
- 【SQLSERVER】存储过程基础
- SQLSERVER 存储过程 语法
- SQLSERVER存储过程基础
- 分页SQLServer存储过程
- SQLSERVER存储过程解密
- SQLSERVER存储过程基础
- 【SQLSERVER】存储过程基础
- SQLServer存储过程分页
- SVN版本控制与分支设置
- FlycoTabLayout使用
- 关于简单的年末员工抽奖
- LeetCode 160. Intersection of Two Linked Lists
- jdk1.7--ArrayList
- 【SQLSERVER】 存储过程
- JSON未定义
- android 蓝牙通信 聊天
- Java网络编程
- 《深入浅出 Java Concurrency》—并发容器 ConcurrentMap
- jquery去掉空格的方法
- ThreadLocal定义、作用
- ?head first ???? ????? ????
- 为何大公司看起来都那么糟?