USE [MyDB];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpsertUser]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UpsertUser]
GO
USE [MyDB];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#AnalysisForUpsertUser]') AND type in (N'U'))
DROP TABLE [dbo].[#AnalysisForUpsertUser]
GO
CREATE TABLE #AnalysisForUpsertUser
(
ExistingUserID int,
ExistingUserName nvarchar(50),
ExistingUserFullName nvarchar(50),
ExistingPassword nvarchar(50),
ExistingState bit,
ExistingEmail nvarchar(50),
ExistingUpdateBy nvarchar(50),
ExistingUpdateTime datetime,
ExistingRemark nvarchar(50),
ActionTaken nvarchar(10),
NewUserID int,
NewUserName nvarchar(50),
NewUserFullName nvarchar(50),
NewPassword nvarchar(50),
NewState bit,
NewEmail nvarchar(50),
NewUpdateBy nvarchar(50),
NewUpdateTime datetime,
NewRemark nvarchar(50),
);
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.UpsertUser
@UserID int,
@UserName nvarchar(50),
@UserFullName nvarchar(50),
@Password nvarchar(50),
@State bit,
@Email nvarchar(50),
@UpdateBy nvarchar(50),
--@UpdateTime datetime,
@Remark nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
MERGE tbUser AS target
USING (SELECT @UserID,@UserName,@UserFullName,@Password,@State,@Email,@UpdateBy,@Remark) AS source
(UserID,UserName,UserFullName,Password,State,Email,UpdateBy,Remark)
ON (target.UserID = source.UserID)
WHEN MATCHED THEN
UPDATE SET
UserName = source.UserName
,UserFullName = source.UserFullName
,Password = source.Password
,State = source.State
,Email = source.Email
,UpdateBy = source.UpdateBy
,UpdateTime = GETDATE()
,Remark = source.Remark
WHEN NOT MATCHED THEN
INSERT (UserID,UserName,UserFullName,Password,State,Email,UpdateBy,UpdateTime,Remark)
VALUES (source.UserID,source.UserName,source.UserFullName,source.Password,source.State,source.Email,source.UpdateBy, GETDATE(),source.Remark)
OUTPUT deleted.*, $action, inserted.* INTO #AnalysisForUpsertUser;
END;
GO
EXEC UpsertUser @UserID = 6887, @UserName = N'test6887', @UserFullName = N'编辑所有测试', @Password = N'YCRrXZzNrNU=',
@State = 1, @Email = N'test3@microsoft.com', @UpdateBy = N'PROGRAM', @Remark = N'可以查看所有项目,并可以修改.';
EXEC UpsertUser @UserID = 6889, @UserName = N'test6889', @UserFullName = N'编辑所有测试', @Password = N'YCRrXZzNrNU=',
@State = 1, @Email = N'test3@microsoft.com', @UpdateBy = N'PROGRAM', @Remark = N'可以查看所有项目,并可以修改.';
EXEC UpsertUser @UserID = 6882, @UserName = N'test6882', @UserFullName = N'编辑所有测试', @Password = N'YCRrXZzNrNU=',
@State = 1, @Email = N'test3@microsoft.com', @UpdateBy = N'PROGRAM', @Remark = N'可以查看所有项目,并可以修改.';
SELECT * FROM [MyDB].[dbo].[tbUser]
SELECT * FROM #AnalysisForUpsertUser
GO;
DROP TABLE #AnalysisForUpsertUser;
GO
设置参数时出错,请仔细查看设置的参数
希望能接纳,谢谢
用户登录
还没有账号?立即注册
用户注册
投稿取消
文章分类: |
|
还能输入300字
上传中....