sqlserver 存儲過程 删除

--删除(delete from)
CREATE PROCEDURE [dbo].[DeleteMessage]
@strtable varchar(50),--要删除資訊的表名
@strwhere varchar(300)--要删除資訊的條件，不用加where，直接：列名=值；如果值是字元串，需加單引号
as
declare @sqlString varchar(500);
declare @whereString varchar(300);
if @strwhere is null or rtrim(@strwhere)=''
    set @whereString='';
else
    set @whereString=' where '+@strwhere;
set @sql= 'delete from '[email protected]+@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(18)=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
*/      

轉載于:https://www.cnblogs.com/zouke1220/p/7459267.html