CREATE PROCEDURE [dbo].EL_SP_KeyLink@UpdateType int,
@KeyID int,
@KeyType int,
@KeyLevel int,
@KeyText nvarchar(255),
@ReplaceText nvarchar(255),
@ArrKeyID nvarchar(4000)WITH ENCRYPTION
AS
SET NOCOUNT ONBEGIN IF @UpdateType=0
BEGIN
BEGIN TRANSACTION
INSERT INTO [EL_KeyLink]([KeyType],[KeyLevel],[KeyText],[ReplaceText])
VALUES(@KeyType, @KeyLevel, @KeyText, @ReplaceText)
SELECT @KeyID=MAX([KeyID]) FROM [EL_KeyLink] IF @@ERROR=0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION END
ELSE IF @UpdateType=1
BEGIN IF NOT EXISTS(SELECT [KeyID] FROM [EL_KeyLink] WHERE [KeyID]=@KeyID)
RETURN 40
UPDATE [EL_KeyLink] SET [KeyLevel]=@KeyLevel,[KeyText]=@KeyText,[ReplaceText]=@ReplaceText WHERE [KeyID]=@KeyID AND [KeyType]=@KeyType END
ELSE IF @UpdateType=2
BEGIN
EXEC('DELETE FROM [EL_KeyLink] WHERE [KeyID] IN('+ @ArrKeyID +')') ENDRETURN
END
go
@KeyID int,
@KeyType int,
@KeyLevel int,
@KeyText nvarchar(255),
@ReplaceText nvarchar(255),
@ArrKeyID nvarchar(4000)WITH ENCRYPTION --存储过程加密
AS
SET NOCOUNT ONBEGIN IF @UpdateType=0 --增加
BEGIN
BEGIN TRANSACTION --开始 一个事务
INSERT INTO [EL_KeyLink]([KeyType],[KeyLevel],[KeyText],[ReplaceText])
VALUES(@KeyType, @KeyLevel, @KeyText, @ReplaceText)
SELECT @KeyID=MAX([KeyID]) FROM [EL_KeyLink] IF @@ERROR=0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION --发现有错回滚事务 END
ELSE IF @UpdateType=1 --更新
BEGIN IF NOT EXISTS(SELECT [KeyID] FROM [EL_KeyLink] WHERE [KeyID]=@KeyID)
RETURN 40 ---(判断这条记录是否存在,不存在返回40,存在则更新这条记录)
UPDATE [EL_KeyLink] SET [KeyLevel]=@KeyLevel,[KeyText]=@KeyText,[ReplaceText]=@ReplaceText WHERE [KeyID]=@KeyID AND [KeyType]=@KeyType END
ELSE IF @UpdateType=2 ---删除
BEGIN
EXEC('DELETE FROM [EL_KeyLink] WHERE [KeyID] IN('+ @ArrKeyID +')') ENDRETURN
END
go
@KeyID int,
@KeyType int,
@KeyLevel int,
@KeyText nvarchar(255),
@ReplaceText nvarchar(255),
@ArrKeyID nvarchar(4000)--CREATE TABLE [EL_KeyLink]([KeyId] INT, [KeyType] INT,[KeyLevel] INT,[KeyText] NVARCHAR(255),[ReplaceText] NVARCHAR(255))WITH ENCRYPTION --存储过程加密
AS
SET NOCOUNT ONBEGIN IF @UpdateType=0 --增加
BEGIN
BEGIN TRANSACTION --开始 一个事务
INSERT INTO [EL_KeyLink]([KeyType],[KeyLevel],[KeyText],[ReplaceText])
VALUES(@KeyType, @KeyLevel, @KeyText, @ReplaceText)
SELECT @KeyID=MAX([KeyID]) FROM [EL_KeyLink] IF @@ERROR=0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION --发现有错回滚事务 END
IF @UpdateType=1 --更新
BEGIN
IF NOT EXISTS(SELECT [KeyID] FROM [EL_KeyLink] WHERE [KeyID]=@KeyID)
RETURN 40 ---(判断这条记录是否存在,不存在返回40,存在则更新这条记录)
ELSE
UPDATE [EL_KeyLink]
SET [KeyLevel]=@KeyLevel,[KeyText]=@KeyText,[ReplaceText]=@ReplaceText
WHERE [KeyID]=@KeyID AND [KeyType]=@KeyType
END IF @UpdateType=2 ---删除
BEGIN
EXEC('DELETE FROM [EL_KeyLink] WHERE [KeyID] IN('''+ @ArrKeyID +''')')
ENDRETURN
END
go