子查询返回多个值？

oknwwptz 于 2021-07-24 发布在 Java

关注(0)|答案(3)|浏览(331)

我正在尝试使用我编写的一个存储过程来在“project”表中创建一个新项目。它还会检查“saved project”表中是否有具有相同id的已保存项目，并在成功创建时将其删除。
它还应该检查传递的用户id是否具有实际创建项目的权限（即不是标准用户）。
以下是存储过程：

USE [BugMate_DB]
GO
/******Object:  StoredProcedure [dbo].[create_project]    Script Date: 2020-07-08 11:05:30 AM******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[create_project]
    @project_name NVARCHAR(40),
    @date_started DATETIME,
    @project_description NVARCHAR(400),
    @project_status NVARCHAR(40),
    @project_active BIT,
    @next_iteration_date DATETIME,
    @created_by_userid INT,
    @project_leader_id INT,
    @save_id INT = NULL
AS

SET NOCOUNT ON

BEGIN
    IF (SELECT TOP 1 roleid from user_info WHERE userid = @created_by_userid) = 'SDU'
    BEGIN
            RAISERROR ('User does not have permission to do this action. Please contact a manager or administrator to resolve this issue.', 16, 1)
    END

    ELSE
    BEGIN
            INSERT INTO project(project_name, date_started, project_description, project_status, project_active, next_iteration_date, created_by_userid, project_leader_id)
            VALUES (@project_name, @date_started, @project_description, @project_status, @project_active, @next_iteration_date, @created_by_userid, @project_leader_id)

            IF @@ERROR <> 0
            BEGIN
                    RAISERROR('Project creation insert failed.', 16, 1)
            END
            ELSE
                BEGIN
                    IF @save_id != NULL
                    BEGIN
                        IF EXISTS (SELECT TOP 1 save_id FROM saved_project WHERE save_id = @save_id)
                        BEGIN
                                DELETE FROM saved_project WHERE save_id = @save_id
                        END
                    END         
                END
        END

        IF @@ERROR <> 0
        BEGIN
                RAISERROR('Error creating project', 16, 1)
        END
        ELSE
        BEGIN
                INSERT INTO project_member(userid, project_number)
                VALUES (@created_by_userid, (SELECT project_number FROM project WHERE created_by_userid = @created_by_userid))
        END
END

问题是，我在执行sp时遇到以下错误：
msg 512，级别16，状态1，过程create\u project，第48行子查询返回了多个值。当子查询后跟=，！=，<，<=，>，>=或者当子查询用作表达式时。
我不知道为什么我的任何子查询都会返回多个值。
以下是我试图传递的值：

USE [BugMate_DB]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[create_project]
        @project_name = N'Test',
        @date_started = N'12/25/2015 12:00:00 AM',
        @project_description = N'This is a test.',
        @project_status = N'InDevelopment',
        @project_active = 1,
        @next_iteration_date = N'12/25/2015 12:00:00 AM',
        @created_by_userid = 19,
        @project_leader_id = 19,
        @save_id = NULL

SELECT  'Return Value' = @return_value

GO

我试过用“Top1”来尝试找回一个值，但它似乎不是我想要的。
据我所知，这也不是一个连接的问题。
我是新来的sql server，所以任何帮助将不胜感激。

sql sql-server Database subquery stored-procedures

来源：https://stackoverflow.com/questions/62800383/subquery-returns-more-than-one-value

3条答案

按热度按时间

i86rm4rw1#

试着改变这个

IF ( SELECT TOP 1 roleid from user_info WHERE userid = @created_by_userid ) = 'SDU'
BEGIN
    RAISERROR ( 'User does not have permission to do this action. Please contact a manager or administrator to resolve this issue.', 16, 1 )
END

到

IF NOT EXISTS ( SELECT * FROM user_info WHERE userid = @created_by_userid AND roleid = 'SDU' )
BEGIN
    RAISERROR ( 'User does not have permission to do this action. Please contact a manager or administrator to resolve this issue.', 16, 1 )
END

编辑：包括显式角色访问。

IF NOT EXISTS ( SELECT * FROM user_info WHERE userid = @created_by_userid AND roleid IN ( 'SDU', 'MNG', 'ADM ' ) )
BEGIN
    RAISERROR ( 'User does not have permission to do this action. Please contact a manager or administrator to resolve this issue.', 16, 1 )
END

更新：

USE [BugMate_DB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[create_project]
    @project_name NVARCHAR(40),
    @date_started DATETIME,
    @project_description NVARCHAR(400),
    @project_status NVARCHAR(40),
    @project_active BIT,
    @next_iteration_date DATETIME,
    @created_by_userid INT,
    @project_leader_id INT,
    @save_id INT = NULL
AS
BEGIN

    SET NOCOUNT ON;
    DECLARE @err INT = 0;
    DECLARE @output TABLE ( project_number INT );

    IF NOT EXISTS ( SELECT * FROM user_info WHERE userid = @created_by_userid AND roleid IN ( 'SDU', 'MNG', 'ADM ' ) )
    BEGIN
            RAISERROR ( 'User does not have permission to do this action. Please contact a manager or administrator to resolve this issue.', 16, 1 );
    END
    ELSE
    BEGIN

        INSERT INTO project (
            project_name, date_started, project_description, project_status, project_active, next_iteration_date, created_by_userid, project_leader_id
        )
        OUTPUT inserted.project_number INTO @output
        VALUES (
            @project_name, @date_started, @project_description, @project_status, @project_active, @next_iteration_date, @created_by_userid, @project_leader_id
        );

        -- Capture error value.
        SET @err = @@ERROR;

        IF @err <> 0
        BEGIN
                RAISERROR ( 'Project creation insert failed.', 16, 1 );
        END
        ELSE
        BEGIN

            IF @save_id IS NULL
            BEGIN

                IF EXISTS ( SELECT * FROM saved_project WHERE save_id IS NULL )
                BEGIN
                    DELETE FROM saved_project WHERE save_id IS NULL;
                END

            END         

        END

    END

    IF @err <> 0
    BEGIN
        RAISERROR ( 'Error creating project', 16, 1 );
    END
    ELSE
    BEGIN

        INSERT INTO project_member ( 
            userid, project_number
        )
        VALUES (
            @created_by_userid, 
            ( SELECT project_number FROM @output )
        );

    END

END

赞(0）回复(0）举报 2021-07-24

c86crjj02#

请比较一下 NULL 值使用 IS NULL 如下所示：

IF @save_id IS NULL

或

IF @save_id IS NOT NULL

赞(0）回复(0）举报 2021-07-24

t5zmwmid3#

我以前从未见过这种类型的查询

INSERT INTO project_member(userid, project_number)
VALUES (@created_by_userid, 
(SELECT project_number FROM project WHERE created_by_userid = @created_by_userid))

我也愿意

INSERT INTO project_member(userid, project_number)
VALUES (@created_by_userid, @project_id)

如果我只是插入两个值。
如果插入许多值 SELECT 而不是 VALUES .
如果我插入的值可能不止一行：

INSERT INTO project_member(userid, project_number)
SELECT created_by_userid, project_number FROM project
WHERE created_by_userid = @created_by_userid

我只是猜测，因为那是关于第46行的
还有，sacse说的 IF (@save_id IS NULL) 也许某个地方有答案

赞(0）回复(0）举报 2021-07-24

我来回答

子查询返回多个值？

3条答案

相关问题

热门标签

最新问答