cs_image_createupdatedelete.prc

解压即可使用

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_Image_CreateUpdateDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_Image_CreateUpdateDelete]
GO



CREATE procedure [dbo].cs_Image_CreateUpdateDelete
(
    @UserID  int,
    @Content image,
    @ContentType nvarchar(64),
    @ContentSize int,
    @Action  int,
    @SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN
    DECLARE @ImageID int

    -- Create
    IF @Action = 0 OR @Action = 1
    BEGIN
        -- Remove if already exists from tables: cs_Images, cs_UserAvatar
        SET @ImageID = (SELECT ImageID FROM cs_UserAvatar WHERE UserID = @UserID and SettingsID = @SettingsID)
        DELETE cs_Images WHERE ImageID = @ImageID and SettingsID = @SettingsID
        DELETE cs_UserAvatar WHERE UserID = @UserID and SettingsID = @SettingsID

        -- Add new entry
        INSERT INTO cs_Images VALUES (@ContentSize, @ContentType, @Content, GetDate(), @SettingsID)
        SET @ImageID = @@Identity
 
        INSERT INTO cs_UserAvatar VALUES (@UserID, @ImageID, @SettingsID)
    END
    ELSE IF @Action = 2
    BEGIN
        -- Remove if already exists from tables: cs_Images, cs_UserAvatar
        SET @ImageID = (SELECT ImageID FROM cs_UserAvatar WHERE UserID = @UserID and SettingsID = @SettingsID)
        DELETE cs_UserAvatar WHERE UserID = @UserID and SettingsID = @SettingsID
        DELETE cs_Images WHERE ImageID = @ImageID and SettingsID = @SettingsID
    END
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Image_CreateUpdateDelete] to public 
go