[T-SQL][存储过程实例]
USE [SchoolDB]GO/****** 对象: Table [dbo].[WZOU_SysPermission] 脚本日期: 10/28/2014 11:05:41 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOEXEC 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'GOEXEC 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'GOEXEC 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'GOEXEC 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'GOEXEC 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 ONset QUOTED_IDENTIFIER ONGO-- =============================================-- 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) = '')ASBEGIN DECLARE @StrSQL NVARCHAR(2000) DECLARE @Count int SELECT @Count=count(*) FROM WZOU_SysPermission WHERE [email protected] AND [email protected] AND [email protected] 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 [email protected] AND [email protected] AND [email protected]) 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