Stored Procedure Error: Ambiguous column

Question

Getting the follow error for each column (note, both tables have the same column names, but I am not sure how to prefix the table data type)

Msg 209, Level 16, State 1, Procedure InsertNonExistingNode, Line 21 Ambiguous column name 'NodeTypeId'.

USE NWatchEntitiesUnitTest
GO

CREATE PROCEDURE InsertNonExistingNode (@TableVariable dbo.NodeTableType READONLY, 
                                        @ScalarParameter nvarchar(255))
AS
BEGIN
INSERT INTO NWatchNodes WITH (ROWLOCK) (
      NodeTypeId,
      Location,
      DisplayName,
      AccessLevel,
      IsEnabled,
      CreatedOn,
      CreatedBy,
      ModifiedOn,
      ModifiedBy,
      NativeId,
      SourceId,
      Name,
      Alias) 

    SELECT 
    NodeTypeId,
    Name,
    Location,
      DisplayName,
      AccessLevel,
      IsEnabled,
      CreatedOn,
      CreatedBy,
      ModifiedOn,
      ModifiedBy,
      NativeId,
      SourceId,
      Alias

    FROM @TableVariable t
        /*Left Join then where ID is null to make sure the record doesn't exists*/
        LEFT JOIN NWatchNodes PR WITH (NOLOCK) 
                                ON  PR.ID = @ScalarParameter
                                AND PR.Name = t.Name
        WHERE PR.ID IS NULL
END

GO

Possible duplicate of [Query error with ambiguous column name in SQL](http://stackoverflow.com/questions/12662954/query-error-with-ambiguous-column-name-in-sql) — Ghassen, Nov 03 '15 at 08:08

score 4 · Answer 1 · answered Nov 03 '15 at 07:57

4

This means you have NodeTypeId column in both @TableVariable and NWatchNodes tables.

In order to resolve it - just use appropriate alias, i.e. t.NodeTypeId or PR.NodeTypeId in select list.

answered Nov 03 '15 at 07:57

Andrey Korneyev

26,353
15
70
71

score 3 · Answer 2 · answered Nov 03 '15 at 08:02

You have problem in aliases, to further understand the problem, that error means you have the same/similar column name in two tables.

TIPS: If you are using aliases on your table, ALWAYS specify the alias name of a specific column so that you will not encounter that error.

Stored Procedure Error: Ambiguous column

2 Answers2