CODEWITHSUNDEEP
sql-serversql-server-2008stored-procedures
user2470174
user2470174

Reputation: 71

SQL Server error.Explicit value for Identity Column....can only be specified when a column list is used and IDENTITY_INSERT is ON

I am trying to create procedure, which is generating an error stating

An explicit value for the identity column in table tblRegisterUser can only be specified when a column list is used and IDENTITY_INSERT is ON.

I tried to surround insert statement with INDENTITY_INSERT to ON,but that too doesn't work. am I missing anything or is it an error with the sub query which i included?

Following is the stored procedure

CREATE PROCEDURE dbo.spInsertUserRegister
(
    @FirstName nvarchar(50),
    @LastName nvarchar(50),
    @Username nvarchar(50),
    @Password nvarchar(50),
    @Designation nvarchar(50),
    @Department nvarchar(50),
    @IsAdmin bit
)
AS
BEGIN
    INSERT INTO tblRegisterUser Values
    (
        @FirstName, @LastName, @Username, @Password,@Designation,@Department,@IsAdmin
    )
    DECLARE @UID INT
    SET @UID = @@IDENTITY
    INSERT INTO tblLogin(Username,Password,UID,IsAdmin) 
    Values(@Username, @Password, @UID,(SELECT IsAdmin FROM tblRegisterUser WHERE     Username=@Username AND Password=@Password))
END

Upvotes: 3

Views: 3843

Answers (2)

SWeko
SWeko

Reputation: 30882

If the structure of the tblRegisterUser table is something like

ID int primary_key autoincrement
FirstName varchar
LastName varchar 
Username varchar
Password varchar
Designation varchar
Department varchar
IsAdmin bit

than this statement is wrong:

INSERT INTO tblRegisterUser Values
(
    @FirstName, @LastName, @Username, @Password, 
    @Designation,@Department,@IsAdmin
)

You should use an explicit column list to specify the columns:

INSERT INTO tblRegisterUser 
(  FirstName, LastName, Username, Password,
   Designation, Department, IsAdmin) 
VALUES 
(
    @FirstName, @LastName, @Username, @Password, 
    @Designation,@Department,@IsAdmin
)

This way the ID field is automatically populated, and the @@Identity statement should return it correctly.

That said, SQL Server has a few functions that return the generated ID for the last rows, each with it's own specific strengths and weaknesses.

Basically:

  • @@IDENTITY works if you do not use triggers
  • SCOPE_IDENTITY() works for the code you explicitly called.
  • IDENT_CURRENT(‘tablename’) works for a specific table, across all scopes.

In almost all scenarios SCOPE_IDENTITY() is what you need, and it's a good habit to use it, opposed to the other options.
A good discussion on the pros and cons of the approaches is also available here.

copied from this answer

Upvotes: 2

Devart
Devart

Reputation: 121902

Try this one -

CREATE PROCEDURE dbo.spInsertUserRegister 
(
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50),
    @Username NVARCHAR(50),
    @Password NVARCHAR(50),
    @Designation NVARCHAR(50),
    @Department NVARCHAR(50),
    @IsAdmin BIT
)
AS BEGIN

    INSERT INTO dbo.tblRegisterUser (FirstName, LastName, Username, [Password], Designation, Department, IsAdmin)
    SELECT @FirstName, @LastName, @Username, @Password, @Designation, @Department, @IsAdmin

    DECLARE @ID BIGINT
    SELECT @ID = SCOPE_IDENTITY()

    --SET IDENTITY_INSERT dbo.tblLogin ON;

    INSERT INTO dbo.tblLogin (UserName, [password], [uid], IsAdmin)
    SELECT @Username, @Password, @ID, IsAdmin 
    FROM tblRegisterUser 
    WHERE UserName = @Username 
        AND [password] = @Password

    --SET IDENTITY_INSERT dbo.tblLogin OFF;

END

Upvotes: 1

Related Questions