使用单个存储过程在单表中插入和更新 [英] insert and update in a singe table with single stored procedure

问题描述

大家好，

如何使用单个存储过程在单表中插入和更新?


感谢

hi all,

How to insert and update in a singe table with single stored procedure?


thanks

推荐答案

以下是使用单个存储过程在单表中插入和更新的示例过程.

Hi,
The following sample procedure to insert and update in a singe table with single stored procedure.

CREATE PROCEDURE [dbo].[emp]
@emp_id varchar(max),@category varchar(max),@emp_cat varchar(max)
AS
BEGIN
SET NOCOUNT ON;

if exists(select * from emp_data where emp_id='''+@emp_id+''' and category='''+@category+''' and emp_cat='''+@emp_cat+''')
exec('UPDATE emp_data set emp_cat=''1231''')
else
exec ('Insert into emp_data(emp_id,category,emp_cat)Values('''+@emp_id+''','''+@category+''','''+@emp_cat+'''')
END
GO

谢谢
坎南
www.steadfastglobal.com

Thanks
Kannan
www.steadfastglobal.com

首先写插入查询而不是更新查询

First write Insert Query than Update Query

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--------------------------------
-- Generated By  : VISHAL WAMAN
--------------------------------
ALTER PROCEDURE [dbo].[usp_InsertUpdateOptions]
	@FK_AdvertisementID INT,
	@ResponseDetails INT
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN
BEGIN
 -- INSERT PART
     INSERT INTO  dbo.LOCATION -- TABLE NAME
		        (
					ResponseDetails,FK_AdvertisementID
				) 
		values
		 	    (
					@ResponseDetails, @FK_AdvertisementID 
				)

-- UPDATE PART
		UPDATE dbo.LOCATION 
		   SET	IsTurnOn = 1
         
END

请参见此 [

See Insert and retrieve data through stored procedure[^]
Or try this[^] out.

这篇关于使用单个存储过程在单表中插入和更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！