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

Viewed 4043

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
2 Answers
Related