首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >修改存储过程以返回数据

问修改存储过程以返回数据
EN

Stack Overflow用户
提问于 2021-10-01 11:57:34
回答 1查看 40关注 0票数 0

我有一个存储过程,它正在查找是否有任何公司具有相同的编号,并返回与此相关的true或false。

这是我的存储过程的代码:

代码语言:javascript
复制
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

我像这样重写了我的存储过程:

代码语言:javascript
复制
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

EN

回答 1

Stack Overflow用户

发布于 2021-10-01 12:09:28

尝试如下所示:

代码语言:javascript
复制
--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来“构建”要作为存储过程的结果返回的结果集。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/69405518

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档