MSSQL

 

1  CASE WHEN 

select MB002 ,case MB002 when '502' THEN 'a' else 'b' end 

  from INVMB

 

 2    触发器 TRIGGER

USE [DEMO]

GO

/****** Object:  Trigger [dbo].[TRITEST]    Script Date: 04/29/2019 18:46:49 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:    <Author,,Name>

-- Create date: <Create Date,,>

-- Description:   <Description,,>

-- =============================================

ALTER TRIGGER  [dbo].[TRITEST] ON [dbo].[INVMB]

   FOR  INSERT,DELETE,UPDATE

AS

 DECLARE @MB002 CHAR(40)

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

     SELECT @MB002 = MB002 FROM inserted

     IF @MB002 = '501'

     BEGIN

    -- Insert statements for trigger here

     raiserror (N'修改后的不能为501',10,1)

     ROLLBACK

     return

    END

   

     SELECT @MB002 = MB002 FROM deleted

     IF @MB002 = '502'

     BEGIN

    -- Insert statements for trigger here

     raiserror (N'名称为502的不能被删除',10,1)

     ROLLBACK

     return

    END 

END

 

3 存储过程

USE [DEMO]

GO

/****** Object:  StoredProcedure [dbo].[testpro]    Script Date: 04/29/2019 19:04:34 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- Batch submitted through debugger: SQLQuery25.sql|7|0|C:\Documents and Settings\Administrator\Local Settings\Temp\~vs49.sql

ALTER proc [dbo].[testpro]

  @MB001 CHAR(20),

  @MB002 VARCHAR(20) 

  AS  

  BEGIN

     SELECT  MB001,MB002,* FROM INVMB

     WHERE    MB002 LIKE '%'+@MB002+'%'  

    END

EXEC  testpro'', '2'