【SQLSERVER】 存储过程

ALTER PROC SP_SinoAirNumberGet
@ShipmentNumber NVARCHAR(50)
AS
BEGIN
	DECLARE @ColoaderShipmentNumber NVARCHAR(50),@ShipmentCode NVARCHAR(50),@errorMessage NVARCHAR(200)
	SET @ColoaderShipmentNumber = ''

	 --check agent shipment
	SELECT @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 shipment
	IF @ShipmentCode IS NULL OR @ShipmentCode = ''
	BEGIN
		SELECT @ShipmentCode = ShipmentCode FROM Shipment s with (nolock)
		INNER JOIN Customer c ON s.CustomerCode = c.CustomerCode AND FranchiseCodeRelate IS NOT NULL 
		WHERE ShipmentNumber = @ShipmentNumber
	END
	
	IF @ShipmentCode IS NULL OR @ShipmentCode = ''
	BEGIN
		RAISERROR('错误的提单号码!',16,1)
		RETURN
	END	

	SELECT @ColoaderShipmentNumber = SinoAirNumber FROM SinoAirNumber with (nolock) WHERE ShipmentCode = @ShipmentCode

	IF @ColoaderShipmentNumber IS NOT NULL AND @ColoaderShipmentNumber != ''
	BEGIN
		SELECT @ColoaderShipmentNumber AS SinoAirNumber
		
		RETURN
	END
	SET @ColoaderShipmentNumber = ''


	IF @ColoaderShipmentNumber IS NULL OR @ColoaderShipmentNumber = ''
	BEGIN
		SELECT Top 1 @ColoaderShipmentNumber = SinoAirNumber
		FROM SinoAirNumber with (nolock)
		WHERE SinoAirNumberStatus != 'Y' OR SinoAirNumberStatus IS NULL
		ORDER BY SinoAirNumber ASC
	END 

	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' ))
	BEGIN
		BEGIN TRANSACTION
		BEGIN TRY
			UPDATE SinoAirNumber SET SinoAirNumberStatus = 'Y',ShipmentCode = @ShipmentCode WHERE SinoAirNumber = @ColoaderShipmentNumber
			UPDATE Shipment SET ShipmentNumber = @ColoaderShipmentNumber,ColoaderNumber=@ShipmentNumber WHERE ShipmentCode = @ShipmentCode
			INSERT INTO ShipmentExtraInfo (ShipmentCode,ShipmentNumber) VALUES (@ShipmentCode,@ColoaderShipmentNumber)
			COMMIT
		END TRY
		BEGIN CATCH
			ROLLBACK
			SET @errorMessage = ERROR_MESSAGE()
			RAISERROR(@errorMessage,1)
		END CATCH
	END
	ELSE
	BEGIN
		SELECT Top 1 @ColoaderShipmentNumber = SinoAirNumber
		FROM SinoAirNumber with (nolock)
		WHERE SinoAirNumberStatus != 'Y' OR SinoAirNumberStatus IS NULL
		ORDER BY SinoAirNumber ASC
	END
	IF @ColoaderShipmentNumber IS NULL OR @ColoaderShipmentNumber = ''
	BEGIN
		RAISERROR('Number pool has empty',1)
		RETURN
	END
	SELECT @ColoaderShipmentNumber AS SinoAirNumber

END

(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,1)? ? ?????? ??errorMessage:需要在前面变量中声明。

END CATCH

（8）.RAISERROR：错误处理

RAISERROR（“errorMessage”,1） ?16:代表错误级别 ? 1：不知道0.0

（9）.RETURN： 结束存储过程。

（编辑：李大同）

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