A nested INSERT, UPDATE, DELETE, or MERGE statement is not allowed in a SELECT statement

Question

I would like to insert a record, then return the details of that record with a stored procedure:

USE 
TEST
GO

CREATE PROCEDURE AddProject(
    -- In
    @_title NVARCHAR(200),
    @_description NVARCHAR(MAX),
    -- Out
    @Title NVARCHAR(200) OUT,
    @Description NVARCHAR(MAX) OUT)
AS
BEGIN
    SELECT 
        [ProjectsTable.ProjectID], 
        [Title], 
        [Description]
    FROM 
        (INSERT INTO [Projects] ("Title", "Description")
         OUTPUT inserted.ProjectID
         VALUES (@_title, @_description)) ProjectsTable
END

I'm getting this error, what am I doing wrong?

Msg 10729, Level 15, State 1, Procedure AddProject, Line 31
A nested INSERT, UPDATE, DELETE, or MERGE statement is not allowed in a SELECT statement that is not the immediate source of rows for an INSERT statement.

Thanks to answer:

The Procedure ended up like this:

USE 
    TEST
GO
    CREATE PROCEDURE AddProject(   

        -- In    
        @_title NVARCHAR(200),
        @_description NVARCHAR(MAX),    
        -- Out    
        @ProjectID INT OUT,
        @Title NVARCHAR(200) OUT,
        @Description NVARCHAR(MAX) OUT

    )
    AS
    BEGIN    

        BEGIN TRAN  

            INSERT INTO     
                [Projects]    
                    ("Title", "Description")
                VALUES    
                    (@_title, @_description)                

            SET @ProjectID = SCOPE_IDENTITY();

            SELECT 
                @Title = [Title], 
                @Description = [Description]                
            FROM 
                [Projects] 
            WHERE 
                [Projects].[ProjectID] = @ProjectID         

        COMMIT

    END

And i called it like this:

Declare @ProjectID as INT
Declare @Title as NVARCHAR(200)
Declare @Description as NVARCHAR(MAX)

EXEC AddProject "Test project", "A test project", @ProjectID output, @Title output, @Description output

SELECT @ProjectID, @Title, @Description

What is the purpose of returning out the same data you're passing in? — George 2.0 Hope, May 12 '17 at 13:55
Insert first, then get the `SCOPE_IDENTITY()` of the ID, then return the `Select` statement. But I agree with @George2.0Hope. — SS_DBA, May 12 '17 at 14:01
@George2.0Hope - Note the extra ProjectID taken from the OUTPUT of the INSERT — Jimmyt1988, May 12 '17 at 14:02
@Hybris95 gives you a correct solution, what was confusing was that your SPROC declaration does not have an output declaration for the ProjectID (or like both Hybris95 or WEI_DBA I would have recommended SCOPE_IDENTITY()) ... However, when I perform this, I wrap it in a transaction and return @@ERROR; as it's important to retrieve the ID, but to handle it gracefully if it fails. — George 2.0 Hope, May 12 '17 at 14:08

score 3 · Accepted Answer · edited May 23 '17 at 12:10

3

Just take the latest inserted Identity to recover Title and Description from your table.

CREATE PROCEDURE AddProject(
    -- In
    @_title NVARCHAR(200),
    @_description NVARCHAR(MAX),
    -- Out
    @Title NVARCHAR(200) OUT,
    @Description NVARCHAR(MAX) OUT,
    @ProjectID INT OUT)
AS
BEGIN
    BEGIN TRAN
        INSERT INTO [Projects] (@_title, @_description);
        SET @ProjectID = SCOPE_INDENTITY();
        SELECT @Title = [Title], @Description = [Description] FROM [Projects] WHERE [Projects.ProjectID] = @ProjectID;
    COMMIT
END

Notice the new ProjectID OUT parameter

SCOPE_IDENTITY

edited May 23 '17 at 12:10

Community

1
1

answered May 12 '17 at 14:02

Hybris95

2,286
2
16
33

Hah, good point, i had missed that part. What about using the OUTPUT keyword... This question is an example of what i might need, but i may need to use the output of the INSERT as i may have several inserts – Jimmyt1988 May 12 '17 at 14:05
You can repeat the transaction as many times as needed, SCOPE_IDENTITY will give you the latest created identity in the current context. I will put the documentation linked to it (check it out) – Hybris95 May 12 '17 at 14:06
Thanks dude. For future reference. Do you know how the query would look using the OUTPUT keyword? – Jimmyt1988 May 12 '17 at 14:07
Nope, but I clearly don't suggest using it. Isolation within a transaction is the best option I can give you – Hybris95 May 12 '17 at 14:08
One last thing. The 2 selects you have made to get the 2 seperate Title and Description properties. Can that be bundled into one select returning both the columns? – Jimmyt1988 May 12 '17 at 14:09
Yes it can... `Select @Title = Title, @Description = Description From [Projects] WHERE [Projects.ProjectID] = @ProjectID` – SS_DBA May 12 '17 at 14:11
I edited the solution according to your factorisation needs – Hybris95 May 12 '17 at 14:59

score 0 · Answer 2 · answered May 12 '17 at 14:14

0

CREATE PROCEDURE AddProject(
    -- In
    @_title NVARCHAR(200),
    @_description NVARCHAR(MAX),
    -- Out
    @Title NVARCHAR(200) OUT,
    @Description NVARCHAR(MAX) OUT,
    @ProjectID INT OUT,
    @err INT OUT)
AS
BEGIN
    BEGIN TRAN
        INSERT INTO [Projects] (@_title, @_description);
        SET @ProjectID = SCOPE_INDENTITY();
        SET @Title = (SELECT [Title] FROM [Projects] WHERE [Projects.ProjectID] = @ProjectID);
        SET @Description = (SELECT [Description] FROM [Projects] WHERE [Projects.ProjectID] = @ProjectID);
        SELECT @err = @@ERROR IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END
    COMMIT
END

answered May 12 '17 at 14:14

George 2.0 Hope

583
1
6
21

I suggest using a TRY CATCH block if an error occurs – Hybris95 May 12 '17 at 14:59
@Hybris95 I guess it depends on if you want to handle the error at the DBA level or the code level, yes? – George 2.0 Hope May 13 '17 at 01:18
Anyway, if an error occurs, considering it can only be the INSERT it would be automatically ROLLBACKed so let the error happen and manage at application level – Hybris95 May 15 '17 at 08:26

A nested INSERT, UPDATE, DELETE, or MERGE statement is not allowed in a SELECT statement

2 Answers2