[T-SQL][存储过程实例]


USE [SchoolDB]
GO
/****** 对象: Table [dbo].[WZOU_SysPermission] 脚本日期: 10/28/2014 11:05:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WZOU_SysPermission](
[TeacherId] [nchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Specialty] [nvarchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ClassName] [nvarchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Permission] [bigint] NOT NULL,
[Remark] [nvarchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_WZOU_SysPermission] PRIMARY KEY CLUSTERED
(
[TeacherId] ASC,
[Specialty] ASC,
[ClassName] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'教师编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WZOU_SysPermission', @level2type=N'COLUMN',@level2name=N'TeacherId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'专业(字典库 DicType=05)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WZOU_SysPermission', @level2type=N'COLUMN',@level2name=N'Specialty'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'班级(字典库 DicType=07)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WZOU_SysPermission', @level2type=N'COLUMN',@level2name=N'ClassName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'权限字段' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WZOU_SysPermission', @level2type=N'COLUMN',@level2name=N'Permission'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WZOU_SysPermission', @level2type=N'COLUMN',@level2name=N'Remark'



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATEPROCEDURE [dbo].[UpdateSysPermission]
-- Add the parameters for the stored procedure here
(
@TeacherId nchar(8),
@Specialty nvarchar(16),
@ClassName nvarchar(16),
@Permission bigint,
@Remark nvarchar(512) = ''
)
AS
BEGIN
DECLARE @StrSQL NVARCHAR(2000)
DECLARE @Count int

SELECT @Count=count(*) FROM WZOU_SysPermission WHERE TeacherId=@TeacherId AND Specialty=@Specialty AND ClassName=@ClassName
SET @StrSQL = 'SELECT * FROM WZOU_SysPermission WHERE TeacherId = N''' + @TeacherId + ' AND Specialty = N'''+ @Specialty +' AND ClassName = N''' + @ClassName

PRINT(@Count)

IF EXISTS(SELECT * FROM WZOU_SysPermission WHERE TeacherId=@TeacherId AND Specialty=@Specialty AND ClassName=@ClassName)
BEGIN
PRINT(@StrSQL)
END

IF (@Count = 0)
BEGIN
SET @StrSQL = 'INSERT INTO WZOU_SysPermission (TeacherId, Specialty, ClassName, Permission, Remark) VALUES(N''' + @TeacherId + ''', N''' + @Specialty + ''', N''' + @ClassName + ''', ' + convert(nvarchar,@Permission) + ', N''' + @Remark + ''')'
END
ELSE
BEGIN
SET @StrSQL = 'UPDATE WZOU_SysPermission SET Permission = ' + convert(nvarchar,@Permission) + ', Remark = N''' + @Remark + ''' WHERE (TeacherId = N''' + @TeacherId + ''' AND Specialty = N''' + @Specialty + ''' AND ClassName = N''' + @ClassName + ''')'
END

PRINT(@StrSQL)
EXEC(@StrSQL)
END





//测试
UpdateSysPermission @TeacherId='00000000',@Specialty=N'会计',@ClassName=N'1班',@Permission= 3

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值