我有一个存储过程,它正在查找是否有任何公司具有相同的编号,并返回与此相关的true或false。
这是我的存储过程的代码:
CREATE PROCEDURE [CoDb].[CompanyAttribute_RunDataValidation]
@AttributeId INT,
@Value VARCHAR(255),
@ValidationType SMALLINT,
@Operation SMALLINT,
@CompanyId INT
AS
BEGIN
DECLARE @ValidationSuccess AS BIT = 1
--Create operation
IF @Operation = 1
BEGIN
--Unique validation
IF @ValidationType = 1
BEGIN
IF EXISTS(SELECT 1 FROM [CoDb].[CompanyAttribute] CA
WHERE CA.AttributeId = @AttributeId
AND CA.Value = @Value AND CA.IsDeleted = 0)
SET @ValidationSuccess = 0
END
END
--Update operation
ELSE IF @Operation = 2
BEGIN
--Unique validation
IF @ValidationType = 1
BEGIN
IF EXISTS(SELECT 1 FROM [CoDb].[CompanyAttribute] CA
WHERE CA.AttributeId = @AttributeId
AND CA.Value = @Value AND CA.CompanyId <> @CompanyId
AND CA.IsDeleted = 0)
SET @ValidationSuccess = 0
END
END
SELECT @ValidationSuccess
END现在它只返回true或false。
我希望它返回2个值:1- true或false和所选公司的companyName
我像这样重写了我的存储过程:
CREATE PROCEDURE [CoDb].[CompanyAttribute_RunDataValidation]
@AttributeId INT,
@Value VARCHAR(255),
@ValidationType SMALLINT,
@Operation SMALLINT,
@CompanyId INT
AS
BEGIN
DECLARE @ValidationSuccess AS BIT = 1
DECLARE @CompanyName AS VARCHAR(255) = ''
--Create operation
IF @Operation = 1
BEGIN
--Unique validation
IF @ValidationType = 1
BEGIN
IF EXISTS(SELECT *
FROM [CoDb].[CompanyAttribute] CA
INNER JOIN [CoDb].[Company] C on C.Id = CA.CompanyId
WHERE CA.AttributeId = @AttributeId
AND CA.Value = @Value AND CA.IsDeleted = 0)
SET @ValidationSuccess = 0
SET @CompanyName =
END
END
--Update operation
ELSE IF @Operation = 2
BEGIN
--Unique validation
IF @ValidationType = 1
BEGIN
IF EXISTS(SELECT *
FROM [CoDb].[CompanyAttribute] CA
INNER JOIN [CoDb].[Company] C on C.Id = CA.CompanyId
WHERE CA.AttributeId = @AttributeId
AND CA.Value = @Value AND CA.CompanyId <> @CompanyId
AND CA.IsDeleted = 0)
SET @ValidationSuccess = 0
END
END
SELECT @ValidationSuccess
END而是如何从select中获取字段,例如CompanyName
发布于 2021-10-01 12:09:28
尝试如下所示:
--Create operation
IF @Operation = 1
BEGIN
--Unique validation
IF @ValidationType = 1
IF EXISTS (SELECT *
FROM [CoDb].[CompanyAttribute] CA
INNER JOIN [CoDb].[Company] C ON C.Id = CA.CompanyId
WHERE CA.AttributeId = @AttributeId
AND CA.Value = @Value AND CA.IsDeleted = 0)
BEGIN
SELECT
C.CompanyName, 0 AS ValidationSuccess
FROM [CoDb].[Company] C
WHERE C.Id = @CompanyId
RETURN
END
END您基本上需要使用SELECT来“构建”要作为存储过程的结果返回的结果集。
https://stackoverflow.com/questions/69405518
复制相似问题