CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Rakesh
Rakesh

Reputation: 2790

Stored procedure select max value and insert

I am trying to select max: value from a table and insert value into same table.

The code is:

CREATE PROCEDURE [dbo].[InsertLogin]
    @LOG_ID INT OUTPUT,
    @LOG_NAME VARCHAR(100),
    @LOG_EMAIL VARCHAR(100)
AS
    INSERT INTO login(LOG_NAME, LOG_EMAIL) 
    VALUES(@LOG_NAME, @LOG_EMAIL)

    SET @LOG_ID = @@IDENTITY

The other values are inserting except LOG_ID its getting null.

Upvotes: 0

Views: 448

Answers (1)

sgeddes
sgeddes

Reputation: 62851

My guess is your Log_Id column in your Login table is not setup to be an Identity.

Through T-SQL, you have to drop and readd the column:

alter table login
drop column log_id

alter table login
alter column log_id int not null Identity(1,1)

Alternatively you can do this pretty easily in SSMS. Here's a decent article on the subject:

http://blog.sqlauthority.com/2009/05/03/sql-server-add-or-remove-identity-property-on-column/

I'd also recommend using SCOPE_IDENTITY() over @@Identity

Upvotes: 1

Related Questions