auto increment ID column in Sql.

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008

my stored procedure is as follows:

SQL

ALTER procedure [dbo].[spAllinone]
(
@event tinyint,
@Empid int Identity(1,1) PRIMARY KEY NOT NULL,
@Empname nvarchar(50)=null,
@Salary int=null,
@City nvarchar(50)=null
)

as
if @event = 1
begin
insert into employee_tab(Empname,Salary,City)
values (@Empname,@Salary,@City)
end

else if @event = 2
begin
update employee_tab
set Empname = @Empname,Salary = @Salary,City = @City
where (Empid = @Empid)
End

else if @event = 3
begin
delete from employee_tab
where (Empid = @Empid)
End

but when execute it ,it gives me error like this:

Msg 156, Level 15, State 1, Procedure spAllinone, Line 4
Incorrect syntax near the keyword 'Identity'.
Msg 137, Level 15, State 2, Procedure spAllinone, Line 14
Must declare the scalar variable "@Empname".
Msg 137, Level 15, State 2, Procedure spAllinone, Line 20
Must declare the scalar variable "@Empname".
Msg 137, Level 15, State 2, Procedure spAllinone, Line 27
Must declare the scalar variable "@Empid"
What i want is to auto increase the column Empid value. i have a web application on visual studio 2008 . i dont want to pass the Empid . only i want to pass Empname,Salary,City when performing insert operation.Thanks.

Posted 9-Jun-13 21:46pm

Rambo_Raja

Updated 9-Jun-13 22:06pm

Aarti Meswania

v2

Add a Solution

Comments

kumar2413 10-Jun-13 4:08am

@Empname nvarchar(50) is enough no need of = symbol all

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sanchit patne · Answer 1 · 2013-06-09T22:10:00

1)You should try below query. If you have already created procedure then use 'ALTER PROCEDURE' else use 'CREATE PROCEDURE'.

2)You just need to remove 'Identity(1,1) PRIMARY KEY NOT NULL' portion from parameter of SP.
Please let me know if this helps. Thanks.

SQL

ALTER procedure [dbo].[spAllinone]
(
@event tinyint,
@Empid int,
@Empname nvarchar(50)=null,
@Salary int=null,
@City nvarchar(50)=null
)
 
as
if @event = 1
begin
insert into employee_tab(Empname,Salary,City)
values (@Empname,@Salary,@City)
end
 
else if @event = 2
begin
update employee_tab
set Empname = @Empname,Salary = @Salary,City = @City
where (Empid = @Empid)
End
 
else if @event = 3
begin
delete from employee_tab
where (Empid = @Empid)
End

[edit]Code block added[/edit]

sanchit patne · Answer 2 · 2013-06-10T21:51:00

As your Empid column of table employee_tab is identity(auto incremented), then no need to specify that column while insertion. You need to specify columns other than identity. Identity column will inserted automatically. e.g.

SQL

insert into employee_tab(Empname,Salary,City)
values (@Empname,@Salary,@City)

In this query, you just need to pass three parameters. i.e.@Empname,@Salary,@City.
Empid will be inserted automatically.

If you want to make Empid as identity column.
1) Modify the table emplyee_tab.
2)after clicking on empid column,you can see column properties below.
3)set Is Identity(under Identity specification tab) property to true.

kumar2413 · Answer 3 · 2013-06-09T22:10:00

Solution 2

Sql Server - How to write a Stored procedure in Sql server[^]
Have a look in this site will help u better

Posted 9-Jun-13 22:10pm

kumar2413

Aarti Meswania · Answer 4 · 2013-06-09T22:10:00

Try this...

SQL

ALTER procedure [dbo].[spAllinone]
(
@event tinyint,
@Empid int ,
@Empname nvarchar(50)=null,
@Salary int=null,
@City nvarchar(50)=null
) 
as
BEGIN
    if @event = 1
    begin
    insert into employee_tab(Empname,Salary,City)
    values (@Empname,@Salary,@City)
    end
     
    else if @event = 2
    begin
    update employee_tab
    set Empname = @Empname,Salary = @Salary,City = @City
    where (Empid = @Empid)
    End
     
    else if @event = 3
    begin
    delete from employee_tab
    where (Empid = @Empid)
    End
END

Happy Coding!
:)

auto increment ID column in Sql.

4 solutions

Solution 1

Solution 4

Solution 2

Solution 3

Add your solution here

Preview 0