無返回值的存儲過程
CREATE PROCEDURE [dbo].[ps_PD_WeightBridgeDataRecord]//創(chuàng)建存儲過程
@parameter_name AS VARCHAR(50)//參數(shù)
AS
INSERT INTO dbo.t_PD_WeightBridgeDataRecord//插入的數(shù)據(jù)表
( weighBridgeData)
VALUES ( @parameter_name )
GO
有返回值的存儲過程
ALTER PROCEDURE dbo.usp_SA_isFocusPackName
@orderNo VARCHAR(50),
@newby VARCHAR(20),
@PackFilmCode VARCHAR(5) OUTPUT,//需要返回的數(shù)據(jù)因俐，可以同時返回多個數(shù)據(jù)
@PackPeperCode VARCHAR(5) OUTPUT
AS
SET @PackFilmCode = ( //SET用作給output數(shù)據(jù)賦值
SELECT COUNT(*) FROM dbo.t_BI_Packing AS tbp
WHERE tbp.PackName IN(//如果子查詢結(jié)果有多個列用IN
(
SELECT vsod.PackFilmCode FROM dbo.v_SA_OrderD AS vsod WHERE vsod.Ordno =
(
SELECT vso.Ordno FROM dbo.v_SA_Order AS vso WHERE vso.PreOrderNo = @orderNo
)
)
)
AND tbp.IsFocus = '1'
)
SET @PackPeperCode = (
SELECT COUNT(*) FROM dbo.t_BI_Packing AS tbp
WHERE tbp.PackName IN(
(
SELECT vsod.PackPaperCode FROM dbo.v_SA_OrderD AS vsod WHERE vsod.Ordno =
(
SELECT vso.Ordno FROM dbo.v_SA_Order AS vso WHERE vso.PreOrderNo = @orderNo
)
)
)
AND tbp.IsFocus = '1'
)
GO
返回一個集合的存儲過程
//直接select結(jié)果即可
ALTER PROCEDURE [dbo].[usp_SA_PackNameFocusPersons]
AS
SELECT vhpd.EmpID FROM dbo.v_HR_PersonDept AS vhpd WHERE vhpd.DeptName = '成品包裝儲運部'AND vhpd.PosType = '后勤人員'
GO
傳入的參數(shù)為一個集合的存儲過程
ALTER PROCEDURE [dbo].[usp_SS_WeixinOutlook]
@tt JMTable READONLY ,//傳入的是一個二維數(shù)組捞镰，JMTable可以插入多個數(shù)組誉察，這個是自定義的一個參數(shù)類型
@EdtBy VARCHAR(50)
AS
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
-------需要發(fā)送的消息集合
DECLARE @Send TABLE
(
info_user NVARCHAR(50) ,
info_name NVARCHAR(1000) ,
sheet_no NVARCHAR(50)
)
INSERT INTO @Send ( info_user ,
info_name ,
sheet_no )
SELECT T.c1 ,
c2 ,
T.c3
FROM @tt AS T
IF EXISTS ( SELECT *
FROM @Send AS S )
BEGIN
------寫入接口 邏輯業(yè)務(wù)
END
ELSE
BEGIN
RAISERROR('待發(fā)送消息列表異常', 16, 1)
END
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END;
EXEC dbo.sp_SS_ThrowError;
END CATCH;
GO
