当前位置: 代码迷 >> SQL >> [T-SQL][存储过程范例]
  详细解决方案

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

热度:77   发布时间:2016-05-05 11:06:39.0
[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
  相关解决方案