IF OBJECT_ID(N'BRIEF_GetFileData', N'P') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[BRIEF_GetFileData]
END
GO

CREATE PROCEDURE [dbo].[BRIEF_GetFileData] (@FilePath NVARCHAR(MAX))
AS
/*******************************************************************************************************
*																				                       *
*                                                                                                      *
* Stored Procedure Name : [dbo].[BRIEF_GetFileData]										  			   *
* Copied from			: [dbo].[KAAS_GetFileData]										               *
*                                                                                                      *
* Modification History:                                                                                *
* 2022-07-01    Balamurugan C        Created                                                           *
 *******************************************************************************************************/ 
BEGIN
 DECLARE @NCOMMAND NVARCHAR(MAX)
 DECLARE @result INT
 DECLARE @UNCFilePath NVARCHAR(256)

 SET @FilePath = REPLACE(@FilePath, '''', '''''');

  SET @UNCFilePath = (SELECT dbo.BRIEF_FN_GetUNCPathFromFile(@FilePath))

 SET @NCOMMAND = 'EXEC Master.dbo.xp_fileexist ''' + @UNCFilePath + ''', @result OUTPUT PRINT @result'
 EXECUTE sp_executesql @NCOMMAND, N'@result INT OUTPUT', @result = @result OUTPUT

IF (@result = 1)
 BEGIN
  SET @NCOMMAND = 'DECLARE @file AS VARBINARY(MAX);
				    SELECT @file = CAST(bulkcolumn AS VARBINARY(MAX)) 
				      FROM OPENROWSET (BULK ''' + @UNCFilePath + ''', SINGLE_BLOB) AS x;
				    SELECT @file AS FileData'
  EXECUTE sp_executesql @NCOMMAND
 END
END
GO