sql - INSERT 存储过程的返回值
问题描述
我正在尝试为存储过程设置INSERT
一个RETURN
值,但我无法让它工作。
我有一张"person.person_ids"
用字段调用的表"id int, uid uniqueidentifier"
和一堆其他的表,例如"person.employee"
,"person.client"
等等。这些表都从表中获取它们"id"
的 s 作为外键"person.person_ids"
。
该程序执行此操作:
CREATE PROCEDURE PERSON.NewPerson
AS
BEGIN
DECLARE @ret INT;
INSERT INTO PERSON.ID_PERSON
VALUES (NEWID());
SET @ret = SCOPE_IDENTITY()
RETURN @ret;
END
GO
我的INSERT
样子是这样的:
INSERT INTO PERSON.EMPLOYEE VALUES
(EXECUTE PERSON.NewPerson, 1, '15434235', '10768348153', '1962-3-2', '1999-10-2', 'PETER', '', 'SMITH', 'HAMMER')
GO
基本上,我正在尝试创建一个函数存储过程,"person.person_ids"
每当我将新记录插入"person.employee"
.
我尝试使用OUTPUT
fromINSERT
但我无法让它工作并且 INSERT 不允许在函数内部。
解决方案
这是您修复了问题和一些建议改进的代码。评论解释了什么和为什么。
SP:
CREATE PROCEDURE PERSON.NewPerson
(
-- Use an output parameter to get values out of an SP
@NewId INT OUT
)
AS
BEGIN
-- Recommended to always list the columns you are inserting to
-- Personally my preference is to select them (because that scales to multiple inserts), I never use the 'values' clause.
INSERT INTO PERSON.ID_PERSON (uid)
SELECT NEWID();
SET @NewId = SCOPE_IDENTITY();
-- The return statement is for a status for the SP, usually 0 for success, some other int for an error
RETURN 0;
END
GO
调用 SP:
DECLARE @MyNewId INT;
-- Run the SP before your insert to get your new value
EXEC PERSON.NewPerson @MyNewId OUT;
-- Then insert - ideally with a list of columns
INSERT INTO PERSON.EMPLOYEE
SELECT @MyNewId, 1, '15434235', '10768348153', '1962-3-2', '1999-10-2', 'PETER', '', 'SMITH', 'HAMMER'