[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