[MSSQL] BLOB Insert/Update 예제

반응형

CREATE TABLE [dbo].[TB_SNP_IMAGE](
 [PID] [bigint] IDENTITY(1,1) NOT NULL,
 [FILEPATH] [varchar](1024) NULL,
 [w] [decimal](25, 7) NULL,
 [h] [decimal](25, 7) NULL,
 [c] [varchar](50) NULL,
 [IMG] [varbinary](max) NULL,
 [ETC] [varchar](1024) NULL,
 [YN] [char](1) NULL CONSTRAINT [DF_TB_SNP_IMAGE_YN]  DEFAULT ('N'),
 [YN_DATE] [datetime] NULL,
 [REG_DATE] [datetime] NULL CONSTRAINT [DF_TB_SNP_IMAGE_REG_DATE]  DEFAULT (getdate()),
 [CDATE] [datetime] NULL CONSTRAINT [DF_TB_SNP_IMAGE_CDATE]  DEFAULT (getdate()),
 CONSTRAINT [PK_TB_SNP_IMAGE] PRIMARY KEY CLUSTERED
(
 [PID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

 

-- SP_SNPIMAGE_INSERT 'C:\Users\Public\Pictures\Sample Pictures\Koala.jpg', NULL,NULL,NULL,NULL,NULL,1
-- =============================================
CREATE PROCEDURE [dbo].[SP_SNPIMAGE_INSERT]
 @V_FILEPATH VARCHAR(1024),
 @V_IMG VARBINARY(MAX)=NULL,
 @V_DESC VARCHAR(1024)=NULL,
 @V_W DECIMAL(30,10)=NULL,
 @V_H DECIMAL(30,10)=NULL,
 @V_C VARCHAR(50)=NULL,
 @V_DEBUG INT=0
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE @RETURN INT=1
 DECLARE @V_SQL VARCHAR(MAX)
 DECLARE @PID BIGINT
 DECLARE @CDATE DATETIME

 IF dbo.FN_NullCheck(@V_FILEPATH)=1
  --OR dbo.FN_NullCheck(@V_IMG)=1
 BEGIN
  SELECT 0 'RETVAL', 0 'PID', @V_FILEPATH 'FILEPATH', '이미지정보가 없어서 등록(또는 수정)에 실패했습니다.' 'MSG'
  RETURN
 END

 IF EXISTS (SELECT PID FROM TB_SNP_IMAGE WHERE [FILEPATH]=@V_FILEPATH)
 BEGIN
  SELECT TOP 1 @CDATE=[CDATE]
  FROM TB_SNP_IMAGE
  WHERE [FILEPATH]=@V_FILEPATH -- AND YN='N'
  ORDER BY REG_DATE DESC

  SET @V_SQL = 'UPDATE TB_SNP_IMAGE SET '

  SET @V_SQL = @V_SQL + 'YN=''Y'''
  SET @V_SQL = @V_SQL + ',YN_DATE=GETDATE() '
  SET @V_SQL = @V_SQL + 'WHERE [FILEPATH]='''+@V_FILEPATH+''' AND YN=''N'''

  IF @V_DEBUG=0
   EXECUTE (@V_SQL)
  ELSE
   PRINT @V_SQL

  SET @RETURN = 2
 END

 -- Insert statements for procedure here
 SET @V_SQL = 'INSERT INTO TB_SNP_IMAGE ([FILEPATH],[w],[h],[c],[ETC],[YN],[YN_DATE],[REG_DATE],[CDATE]) VALUES ('
 SET @V_SQL = @V_SQL + ''''+@V_FILEPATH+''''

 SET @V_SQL=@V_SQL+CASE WHEN dbo.FN_NullCheck(@V_W)=1 THEN ',NULL' ELSE ', '+CONVERT(VARCHAR,@V_W) END
 SET @V_SQL=@V_SQL+CASE WHEN dbo.FN_NullCheck(@V_H)=1 THEN ',NULL' ELSE ', '+CONVERT(VARCHAR,@V_H) END
 SET @V_SQL=@V_SQL+CASE WHEN dbo.FN_NullCheck(@V_C)=1 THEN ',NULL' ELSE ', '''+@V_C+'''' END
 SET @V_SQL=@V_SQL+CASE WHEN dbo.FN_NullCheck(@V_DESC)=1 THEN ',NULL' ELSE ', '''+@V_DESC+'''' END
 SET @V_SQL=@V_SQL+',''N'',NULL,GETDATE(),'''+CONVERT(VARCHAR(23),GETDATE(),121)+''')'

 IF @V_DEBUG=0
  EXECUTE (@V_SQL)
 ELSE
  PRINT @V_SQL

 --SELECT @PID = SCOPE_IDENTITY()--@@IDENTITY
 SELECT @PID = IDENT_CURRENT('TB_SNP_IMAGE')
 --SELECT SCOPE_IDENTITY()

 IF @PID IS NOT NULL
 BEGIN
  IF dbo.FN_NullCheck(@V_IMG)=0
   UPDATE TB_SNP_IMAGE SET [IMG]=@V_IMG WHERE [PID]=@PID
  ELSE
  BEGIN
   SET @V_SQL = 'UPDATE TB_SNP_IMAGE '+CHAR(13)
   SET @V_SQL = @V_SQL+'SET [IMG]=('+CHAR(13)
   SET @V_SQL = @V_SQL+' SELECT BULKCOLUMN FROM OPENROWSET(BULK '''+@V_FILEPATH+''', SINGLE_BLOB) AS IMG)'+CHAR(13)
   SET @V_SQL = @V_SQL+'WHERE [PID]='+CONVERT(VARCHAR,@PID)
   
   IF @V_DEBUG=0
    EXECUTE (@V_SQL)
   ELSE
    PRINT @V_SQL
  END

  SELECT @RETURN 'RETVAL', @PID 'PID', @V_FILEPATH 'FILEPATH', '이미지정보 '''+@V_FILEPATH+''' 등록에 성공했습니다.' 'MSG'
 END
 ELSE
  SELECT 0 'RETVAL', 0 'PID', @V_FILEPATH 'FILEPATH', '이미지정보 등록(또는 수정)에 실패했습니다.' 'MSG'

END

출처: https://devdb.tistory.com/14 [DB 관련:티스토리]