📄 存储过程.sql
字号:
SET @ReturnValue = @@ROWCOUNT
END
-- 删除
IF (@DB_Option_Action_='Delete')
BEGIN
DELETE sys_Module WHERE (ModuleID = @ModuleID)
SET @ReturnValue = @@ROWCOUNT
END
SELECT @ReturnValue
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- 创建更新删除
CREATE PROCEDURE sys_OnlineInsertUpdateDelete
(
@OnlineID int = 0, -- 自动ID
@O_SessionID varchar(24) = '', -- 用户SessionID
@O_UserName nvarchar(20) = '', -- 用户名
@O_Ip varchar(15) = '', -- 用户IP地址
@O_LoginTime datetime = Null, -- 登陆时间
@O_LastTime datetime = Null, -- 最后访问时间
@O_LastUrl nvarchar(500) = '', -- 最后请求网站
@DB_Option_Action_ nvarchar(20) = '' -- 操作方法 Insert:增加 Update:修改 Delete:删除
)
AS
DECLARE @ReturnValue int -- 返回操作结果
SET @ReturnValue = -1
-- 新增
IF (@DB_Option_Action_='Insert')
BEGIN
INSERT INTO sys_Online(
O_SessionID,
O_UserName,
O_Ip,
O_LoginTime,
O_LastTime,
O_LastUrl
) VALUES (
@O_SessionID,
@O_UserName,
@O_Ip,
@O_LoginTime,
@O_LastTime,
@O_LastUrl
)
SET @ReturnValue = @@IDENTITY
END
-- 更新
IF (@DB_Option_Action_='Update')
BEGIN
UPDATE sys_Online SET
O_SessionID = @O_SessionID,
O_UserName = @O_UserName,
O_Ip = @O_Ip,
O_LoginTime = @O_LoginTime,
O_LastTime = @O_LastTime,
O_LastUrl = @O_LastUrl
WHERE (OnlineID = @OnlineID)
SET @ReturnValue = @@ROWCOUNT
END
-- 删除
IF (@DB_Option_Action_='Delete')
BEGIN
DELETE sys_Online WHERE (OnlineID = @OnlineID)
SET @ReturnValue = @@ROWCOUNT
END
SELECT @ReturnValue
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- 创建更新删除
CREATE PROCEDURE sys_RoleApplicationInsertUpdateDelete
(
@A_RoleID int = 0, -- 角色ID与sys_Roles中RoleID相关
@A_ApplicationID int = 0, -- 应用ID与sys_Applications中Appl
@DB_Option_Action_ nvarchar(20) = '' -- 操作方法 Insert:增加 Update:修改 Delete:删除
)
AS
DECLARE @ReturnValue int -- 返回操作结果
SET @ReturnValue = -1
-- 新增
IF (@DB_Option_Action_='Insert')
BEGIN
INSERT INTO sys_RoleApplication(
A_RoleID,
A_ApplicationID
) VALUES (
@A_RoleID,
@A_ApplicationID
)
SET @ReturnValue = @@ROWCOUNT
END
-- 更新
IF (@DB_Option_Action_='Update')
BEGIN
UPDATE sys_RoleApplication SET
A_RoleID = @A_RoleID,
A_ApplicationID = @A_ApplicationID
WHERE ( A_RoleID= @A_RoleID)
SET @ReturnValue = @@ROWCOUNT
END
-- 删除
IF (@DB_Option_Action_='Delete')
BEGIN
DELETE sys_RoleApplication WHERE ( A_RoleID= @A_RoleID and A_ApplicationID = @A_ApplicationID)
SET @ReturnValue = @@ROWCOUNT
END
SELECT @ReturnValue
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- 创建更新删除
CREATE PROCEDURE sys_RolePermissionInsertUpdateDelete
(
@PermissionID int = 0, -- 角色应用权限自动ID
@P_RoleID int = 0, -- 角色ID与sys_Roles表中RoleID相
@P_ApplicationID int = 0, -- 角色所属应用ID与sys_Applicatio
@P_PageCode varchar(20) = '', -- 角色应用中页面权限代码
@P_Value int = 0, -- 权限值
@DB_Option_Action_ nvarchar(20) = '' -- 操作方法 Insert:增加 Update:修改 Delete:删除
)
AS
DECLARE @ReturnValue int -- 返回操作结果
SET @ReturnValue = -1
-- 新增
IF (@DB_Option_Action_='Insert')
BEGIN
INSERT INTO sys_RolePermission(
P_RoleID,
P_ApplicationID,
P_PageCode,
P_Value
) VALUES (
@P_RoleID,
@P_ApplicationID,
@P_PageCode,
@P_Value
)
SET @ReturnValue = @@ROWCOUNT
END
-- 更新
IF (@DB_Option_Action_='Update')
BEGIN
UPDATE sys_RolePermission SET
P_RoleID = @P_RoleID,
P_ApplicationID = @P_ApplicationID,
P_PageCode = @P_PageCode,
P_Value = @P_Value
WHERE (PermissionID = @PermissionID)
SET @ReturnValue = @@ROWCOUNT
END
-- 删除
IF (@DB_Option_Action_='Delete')
BEGIN
DELETE sys_RolePermission WHERE (PermissionID = @PermissionID)
SET @ReturnValue = @@ROWCOUNT
END
SELECT @ReturnValue
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- 创建更新删除
CREATE PROCEDURE sys_RolesInsertUpdateDelete
(
@RoleID int = 0, -- 角色ID自动ID
@R_RoleName nvarchar(50) = '', -- 角色名称
@R_Description nvarchar(255) = '', -- 角色介绍
@DB_Option_Action_ nvarchar(20) = '' -- 操作方法 Insert:增加 Update:修改 Delete:删除
)
AS
DECLARE @ReturnValue int -- 返回操作结果
SET @ReturnValue = -1
-- 新增
IF (@DB_Option_Action_='Insert')
BEGIN
INSERT INTO sys_Roles(
R_RoleName,
R_Description
) VALUES (
@R_RoleName,
@R_Description
)
SET @ReturnValue = @@ROWCOUNT
END
-- 更新
IF (@DB_Option_Action_='Update')
BEGIN
UPDATE sys_Roles SET
R_RoleName = @R_RoleName,
R_Description = @R_Description
WHERE (RoleID = @RoleID)
SET @ReturnValue = @@ROWCOUNT
END
-- 删除
IF (@DB_Option_Action_='Delete')
BEGIN
DELETE sys_Roles WHERE (RoleID = @RoleID)
SET @ReturnValue = @@ROWCOUNT
END
SELECT @ReturnValue
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- 创建更新删除
CREATE PROCEDURE sys_SystemInfoInsertUpdateDelete
(
@SystemID int = 0, -- 自动ID
@S_Name nvarchar(50) = '', -- 系统名称
@S_Version nvarchar(50) = '', -- 版本号
@S_SystemConfigData image , -- 系统配置信息
@S_Licensed varchar(50) = '', -- 序列号
@DB_Option_Action_ nvarchar(20) = '' -- 操作方法 Insert:增加 Update:修改 Delete:删除
)
AS
DECLARE @ReturnValue int -- 返回操作结果
SET @ReturnValue = -1
-- 新增
IF (@DB_Option_Action_='Insert')
BEGIN
INSERT INTO sys_SystemInfo(
S_Name,
S_Version,
S_SystemConfigData,
S_Licensed
) VALUES (
@S_Name,
@S_Version,
@S_SystemConfigData,
@S_Licensed
)
SET @ReturnValue = @@IDENTITY
END
-- 更新
IF (@DB_Option_Action_='Update')
BEGIN
UPDATE sys_SystemInfo SET
S_Name = @S_Name,
S_Version = @S_Version,
S_SystemConfigData = @S_SystemConfigData,
S_Licensed = @S_Licensed
WHERE (SystemID = @SystemID)
SET @ReturnValue = @@ROWCOUNT
END
-- 删除
IF (@DB_Option_Action_='Delete')
BEGIN
DELETE sys_SystemInfo WHERE (SystemID = @SystemID)
SET @ReturnValue = @@ROWCOUNT
END
SELECT @ReturnValue
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- 创建更新删除
CREATE PROCEDURE sys_UserInsertUpdateDelete
(
@UserID int = 0, -- 用户ID号
@U_LoginName nvarchar(20) = '', -- 登陆名
@U_Password varchar(32) = '', -- 密码md5加密字符
@U_CName nvarchar(20) = '', -- 中文姓名
@U_EName varchar(50) = '', -- 英文名
@U_GroupID int = 0, -- 部门ID号与sys_Group表中GroupID关联
@U_Email varchar(100) = '', -- 电子邮件
@U_Type tinyint = 0, -- 用户类型0:超级用户1:普通用户
@U_Status tinyint = 0, -- 当前状态0:正常 1:禁止
@U_Licence varchar(30) = '', -- 用户序列号
@U_Mac varchar(50) = '', -- 锁定机器硬件地址
@U_Remark nvarchar(200) = '', -- 备注说明
@U_IDCard varchar(30) = '', -- 身份证号码
@U_Sex tinyint = 0, -- 性别1:男0:女
@U_BirthDay datetime = Null, -- 出生日期
@U_MobileNo varchar(15) = '', -- 手机号
@U_UserNO varchar(20) = '', -- 员工编号
@U_WorkStartDate datetime = Null, -- 到职日期
@U_WorkEndDate datetime = Null, -- 离职日期
@U_CompanyMail varchar(255) = '', -- 公司邮件地址
@U_Title int = 0, -- 职称与应用字段关联
@U_Extension varchar(10) = '', -- 分机号
@U_HomeTel varchar(20) = '', -- 家中电话
@U_PhotoUrl nvarchar(255) = '', -- 用户照片网址
@U_DateTime datetime = Null, -- 操作时间
@U_LastIP varchar(15) = '', -- 最后访问IP
@U_LastDateTime datetime = Null, -- 最后访问时间
@U_ExtendField ntext = '', -- 扩展字段
@DB_Option_Action_ nvarchar(20) = '' -- 操作方法 Insert:增加 Update:修改 Delete:删除
)
AS
DECLARE @ReturnValue int -- 返回操作结果
SET @ReturnValue = -1
-- 新增
IF (@DB_Option_Action_='Insert')
BEGIN
INSERT INTO sys_User(
U_LoginName,
U_Password,
U_CName,
U_EName,
U_GroupID,
U_Email,
U_Type,
U_Status,
U_Licence,
U_Mac,
U_Remark,
U_IDCard,
U_Sex,
U_BirthDay,
U_MobileNo,
U_UserNO,
U_WorkStartDate,
U_WorkEndDate,
U_CompanyMail,
U_Title,
U_Extension,
U_HomeTel,
U_PhotoUrl,
U_DateTime,
U_LastIP,
U_LastDateTime,
U_ExtendField
) VALUES (
@U_LoginName,
@U_Password,
@U_CName,
@U_EName,
@U_GroupID,
@U_Email,
@U_Type,
@U_Status,
@U_Licence,
@U_Mac,
@U_Remark,
@U_IDCard,
@U_Sex,
@U_BirthDay,
@U_MobileNo,
@U_UserNO,
@U_WorkStartDate,
@U_WorkEndDate,
@U_CompanyMail,
@U_Title,
@U_Extension,
@U_HomeTel,
@U_PhotoUrl,
@U_DateTime,
@U_LastIP,
@U_LastDateTime,
@U_ExtendField
)
SET @ReturnValue = @@IDENTITY
END
-- 更新
IF (@DB_Option_Action_='Update')
BEGIN
UPDATE sys_User SET
U_LoginName = @U_LoginName,
U_Password = @U_Password,
U_CName = @U_CName,
U_EName = @U_EName,
U_GroupID = @U_GroupID,
U_Email = @U_Email,
U_Type = @U_Type,
U_Status = @U_Status,
U_Licence = @U_Licence,
U_Mac = @U_Mac,
U_Remark = @U_Remark,
U_IDCard = @U_IDCard,
U_Sex = @U_Sex,
U_BirthDay = @U_BirthDay,
U_MobileNo = @U_MobileNo,
U_UserNO = @U_UserNO,
U_WorkStartDate = @U_WorkStartDate,
U_WorkEndDate = @U_WorkEndDate,
U_CompanyMail = @U_CompanyMail,
U_Title = @U_Title,
U_Extension = @U_Extension,
U_HomeTel = @U_HomeTel,
U_PhotoUrl = @U_PhotoUrl,
U_DateTime = @U_DateTime,
U_LastIP = @U_LastIP,
U_LastDateTime = @U_LastDateTime,
U_ExtendField = @U_ExtendField
WHERE (UserID = @UserID)
SET @ReturnValue = @UserID
END
-- 删除
IF (@DB_Option_Action_='Delete')
BEGIN
DELETE sys_User WHERE (UserID = @UserID)
SET @ReturnValue = @@ROWCOUNT
END
SELECT @ReturnValue
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- 创建更新删除
CREATE PROCEDURE sys_UserRolesInsertUpdateDelete
(
@R_UserID int = 0, -- 用户ID与sys_User表中UserID相关
@R_RoleID int = 0, -- 用户所属角色ID与Sys_Roles关联
@DB_Option_Action_ nvarchar(20) = '' -- 操作方法 Insert:增加 Update:修改 Delete:删除
)
AS
DECLARE @ReturnValue int -- 返回操作结果
SET @ReturnValue = -1
-- 新增
IF (@DB_Option_Action_='Insert')
BEGIN
INSERT INTO sys_UserRoles(
R_UserID,
R_RoleID
) VALUES (
@R_UserID,
@R_RoleID
)
SET @ReturnValue = @@ROWCOUNT
END
-- 更新
IF (@DB_Option_Action_='Update')
BEGIN
UPDATE sys_UserRoles SET
R_UserID = @R_UserID,
R_RoleID = @R_RoleID
WHERE ( R_UserID = @R_UserID and R_RoleID = @R_RoleID)
SET @ReturnValue = @@ROWCOUNT
END
-- 删除
IF (@DB_Option_Action_='Delete')
BEGIN
DELETE sys_UserRoles WHERE ( R_UserID = @R_UserID and R_RoleID = @R_RoleID)
SET @ReturnValue = @@ROWCOUNT
END
SELECT @ReturnValue
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -