如何编写单个存储过程进行插入和更新? [英] How to write single stored procedure for inserting and update?
问题描述
在sql数据库中插入和更新数据时,我编写了两个不同的存储过程。但我需要编写单个存储过程。
但是在那个表中postID是自动增量id。很少有人建议我使用scope_identity。
但如何使用它?
以下是我的sp:
CREATE PROCEDURE SP_ADD_NEW_JOB_POSTING
@Jobpostid int输出,
@Heading Varchar(50),
@Postedon日期,
@Postedby Varchar(15),
@Description Varchar(Max),
@Position Varchar(15),
@JobType int,
@TotalPositions int,
@Experience nvarchar(20),
@Qualification Varchar( Max),
@Salary float,
@RequiredSkills Varchar(Max),
@Location varchar(15),
@EmailId nvarchar(30)
AS
设定NOCOUNT ON
BEGIN
如果不是EXISTS(从dbo.TBL_JOBPOSTING选择Jobpostid,其中Jobpostid = @Jobpostid)
begin
插入dbo.TBL_JOBPOSTING
(Heading,Postedon,PostedBy,Description,Position,JobType,TotalPositions,
Experienc e,资格,薪水,RequiredSkills,Location,EmailId)
价值(@Heading,CONVERT(VARCHAR(12),@ Posttedon,107)
,@ Postedby,@ Description,@ Position,@ JobType,@ TotalPositions,@ Experience,
@ Qualification,@ Salary,@ RequiredSkills,@ Location,@ EmailId)
SET @Jobpostid = SCOPE_IDENTITY()
结束
否则开始
UPDATE dbo.TBL_JOBPOSTING
套装
标题= @Heading,
Postedon = @Postedon,
PostedBy = @Postedby,
描述= @Description,
位置= @Position,
JobType = @ JobType,
TotalPositions = @TotalPositions,
经验= @体验,
资格= @资格,
薪资= @Salary,
RequiredSkills = @RequiredSkills,
位置= @Location,
EmailId = @EmailId
Jobpostid = @Jobpostid
e nd
结束
注意:单击提交按钮'@Jobpostid'时出错没有提供。
谢谢和问候,
Murali
Hi,
While inserting and updating data in sql database i have written two different stored procedures. But i need to write single stored procedure.
But in that table "postID" is auto increment id. Few people suggested me to use "scope_identity ".
But how to use that?
Below is my sp:
CREATE PROCEDURE SP_ADD_NEW_JOB_POSTING
@Jobpostid int output,
@Heading Varchar(50),
@Postedon date,
@Postedby Varchar(15),
@Description Varchar(Max),
@Position Varchar(15),
@JobType int,
@TotalPositions int,
@Experience nvarchar(20),
@Qualification Varchar(Max),
@Salary float,
@RequiredSkills Varchar(Max),
@Location varchar(15),
@EmailId nvarchar(30)
AS
SET NOCOUNT ON
BEGIN
IF NOT EXISTS (select Jobpostid from dbo.TBL_JOBPOSTING where Jobpostid = @Jobpostid)
begin
Insert into dbo.TBL_JOBPOSTING
( Heading,Postedon,PostedBy,Description,Position,JobType,TotalPositions,
Experience,Qualification,Salary,RequiredSkills,Location,EmailId)
Values (@Heading, CONVERT(VARCHAR(12), @Postedon, 107)
,@Postedby,@Description,@Position,@JobType,@TotalPositions,@Experience,
@Qualification,@Salary,@RequiredSkills,@Location,@EmailId)
SET @Jobpostid = SCOPE_IDENTITY()
END
else begin
UPDATE dbo.TBL_JOBPOSTING
Set
Heading = @Heading,
Postedon = @Postedon,
PostedBy = @Postedby,
Description = @Description,
Position = @Position,
JobType = @JobType,
TotalPositions = @TotalPositions,
Experience = @Experience,
Qualification = @Qualification,
Salary = @Salary,
RequiredSkills = @RequiredSkills,
Location = @Location,
EmailId = @EmailId
Where Jobpostid = @Jobpostid
end
END
Note: getting error when click "Submit" button '@Jobpostid', which was not supplied.
Thanks and Regards,
Murali
推荐答案
尝试:
INSERT INTO MyTable (MyColumn) VALUES ('hello')
UPDATE MyTable SET MyOtherColumn='World' WHERE postID=SCOPE_IDENTITY()
有一个看看 MERGE [ ^ ]。
上面提到的第一个例子是使用MERGE在单个语句中对表执行INSERT和UPDATE操作。
Have a look at MERGE[^].
The first example in mentioned is "Using MERGE to perform INSERT and UPDATE operations on a table in a single statement".
这篇关于如何编写单个存储过程进行插入和更新?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!