无返回值的存储过程
CREATE PROCEDURE [dbo].[ps_PD_WeightBridgeDataRecord]//创建存储过程
@parameter_name AS VARCHAR(50)//参数
AS
INSERT INTO dbo.t_PD_WeightBridgeDataRecord//插入的数据表
( weighBridgeData)
VALUES ( @parameter_name )
GO
有返回值的存储过程
ALTER PROCEDURE dbo.usp_SA_isFocusPackName
@orderNo VARCHAR(50),
@newby VARCHAR(20),
@PackFilmCode VARCHAR(5) OUTPUT,//需要返回的数据，可以同时返回多个数据
@PackPeperCode VARCHAR(5) OUTPUT
AS
SET @PackFilmCode = ( //SET用作给output数据赋值
SELECT COUNT(*) FROM dbo.t_BI_Packing AS tbp
WHERE tbp.PackName IN(//如果子查询结果有多个列用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结果即可
ALTER PROCEDURE [dbo].[usp_SA_PackNameFocusPersons]
AS
SELECT vhpd.EmpID FROM dbo.v_HR_PersonDept AS vhpd WHERE vhpd.DeptName = '成品包装储运部'AND vhpd.PosType = '后勤人员'
GO
传入的参数为一个集合的存储过程
ALTER PROCEDURE [dbo].[usp_SS_WeixinOutlook]
@tt JMTable READONLY ,//传入的是一个二维数组，JMTable可以插入多个数组，这个是自定义的一个参数类型
@EdtBy VARCHAR(50)
AS
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
-------需要发送的消息集合
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
------写入接口 逻辑业务
END
ELSE
BEGIN
RAISERROR('待发送消息列表异常', 16, 1)
END
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END;
EXEC dbo.sp_SS_ThrowError;
END CATCH;
GO
