Sql中的自动增量ID列。 [英] auto increment ID column in Sql.
问题描述
我的存储过程如下：
my stored procedure is as follows:
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
但是当执行它时，它会给我这样的错误：
消息156，等级15，状态1，过程spAllinone，第4行
关键字'Identity'附近的语法不正确。
消息137，级别15，状态2，过程spAllinone，第14行
必须声明标量变量@Empname。
消息137，级别15，状态2，过程spAllinone，第20行
必须声明标量变量@Empname。
消息137，级别15，状态2，过程spAllinone，行27
必须声明标量变量@Empid
我想要的是自动增加列Empid值。我在visual studio 2008上有一个Web应用程序。我不想通过Empid。只有我想在执行插入操作时传递Empname，Salary，City。谢谢。
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.
推荐答案
1）你应该尝试下面的查询。如果你已经创建了程序，那么使用'ALTER PROCEDURE'，否则使用'CREATE PROCEDURE'。
2）你只需要删除'Identity（1,1）PRIMARY来自SP参数的KEY NOT NULL'部分。
如果有帮助请告诉我。谢谢。
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.
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]已添加代码块[/编辑]
[edit]Code block added[/edit]
由于表employee_tab的Empid列是标识（自动递增），因此在插入时无需指定该列。您需要指定除标识以外的列。标识列将自动插入。例如
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.
insert into employee_tab(Empname,Salary,City)
values (@Empname,@Salary,@City)
In这个查询，你只需要传递三个参数。即@ Empname，@ Salary，@ City。
将自动插入Empid。
如果您想将Empid作为标识列。
1）修改表emplyee_tab。
2）点击empid列后，你可以看到下面的列属性。
3）set Is身份（在身份规范选项卡下）属性为true。
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.
Sql Server - 如何在Sql server中编写存储过程 [ ^ ]
看看这个网站将有助于你更好地
Sql Server - How to write a Stored procedure in Sql server[^]
Have a look in this site will help u better
这篇关于Sql中的自动增量ID列。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
