--删除(delete from)

CREATE PROCEDURE [dbo].[DeleteMessage]

@strtable varchar(),--要删除信息的表名

@strwhere varchar()--要删除信息的条件，不用加where，直接：列名=值；如果值是字符串，需加单引号

as

declare @sqlString varchar();

declare @whereString varchar();

if @strwhere is null or rtrim(@strwhere)=''

    set @whereString='';

else

    set @whereString=' where '+@strwhere;

set @sql= 'delete from '+@strtable+@whereString;

exec(@sql);

--execute [dbo].[DeleteMessage] 'base._Member','ID=554586'

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- 邹柯

create PROCEDURE CollegeDelete

(

    @ceid varchar()=null

)

WITH ENCRYPTION

AS

BEGIN

    SET NOCOUNT     ON;

    SET XACT_ABORT  ON;

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    BEGIN TRY

        BEGIN    TRAN;

        declare @userid int;

        select @userid=TutorId from Certificate where Id=@ceid;

        -- delete Certificate

        delete from Certificate where Id=@ceid

        -- delete Tutor

        delete from Tutor where Id=@userid

        COMMIT    TRAN;

    END TRY

    BEGIN CATCH

        ROLLBACK TRAN; throw;

    END CATCH

END

/*

USE [GmkCollege]

GO

DECLARE    @return_value int

EXEC    @return_value = CollegeDelete

        @ceid = 35

SELECT    'Return Value' = @return_value

GO

*/