[dbo].[create_extDatasource]

/****** Object:  StoredProcedure [dbo].[create_extDatasource]    Script Date: 20/10/2022 11:56:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/***********************************************************
 _____  _____  __     _____  _____  _____  _____  __    
|  |  ||   __||  |   |  _  ||   __||_   _||   __||  |   
|     ||   __||  |__ |     ||__   |  | |  |   __||  |__ 
|__|__||_____||_____||__|__||_____|  |_|  |_____||_____| LTD

************************************************************
** Name:    dbo.sp_sys_create_extDatasource
** Desc:  Create new External Data Source & Credential for the Azure Blob
** Auth:  Julian Kellett
** Date:  2022-07-25
** Purpose: 
** Sample Exec:
**************************
** Change History
**************************
** PR   Date    Author      Description  
** --   --------  -------      ------------------------------------
** 1  2022-07-25  Julian Kellett    Created stored proc.
***********************************************************/
ALTER PROCEDURE [dbo].[create_extDatasource] (@ExtlDS     SYSNAME,
                                                     @SAS_Token  VARCHAR(MAX),
                                                     @URLocation VARCHAR(MAX),
                                                     @Debug      INTEGER = 0)
AS
  BEGIN
      ----------------------------------------------------------------------------------------------------
      --For Error Handling 
      DECLARE @ProcedureName SYSNAME,
              @ErrorMessage  VARCHAR(1000)

      SELECT @ProcedureName = Object_name(@@PROCID),
             @ErrorMessage = NULL

      ----------------------------------------------------------------------------------------------------
      BEGIN TRY
          DECLARE @CrtDSSQL NVARCHAR(MAX),
                  @DrpDSSQL NVARCHAR(MAX),
                  @DBCred   SYSNAME;

          SELECT @DBCred = @ExtlDS + 'Credential'

          SET @DrpDSSQL =
          N'  IF EXISTS ( SELECT 1 FROM sys.external_data_sources WHERE Name = '''
          + @ExtlDS + ''' )  BEGIN  DROP EXTERNAL DATA SOURCE '
          + @ExtlDS
          +
  ' ;  END;    IF EXISTS ( SELECT 1 FROM sys.database_scoped_credentials WHERE Name = '''
                  + @DBCred
                  + ''' )  BEGIN  DROP DATABASE SCOPED CREDENTIAL '
                  + @DBCred + ';  END;  ';
  SET @CrtDSSQL = @DrpDSSQL
                  + N'  CREATE DATABASE SCOPED CREDENTIAL '
                  + @DBCred + '  WITH IDENTITY = ''SHARED ACCESS SIGNATURE'',  SECRET = ''' + @SAS_Token
                  + ''';    CREATE EXTERNAL DATA SOURCE ' + @ExtlDS
                  + '  WITH (  TYPE = BLOB_STORAGE,  LOCATION = ''' + @URLocation
                  + ''' ,  CREDENTIAL = ' + @DBCred + '  );  ';

  IF @Debug IN ( 1 )
    PRINT @CrtDSSQL;

  EXEC (@CrtDSSQL);
  END TRY

      ----------------------------------------------------------------------------------------------------
      --Error catch
      BEGIN CATCH
          IF @@TRANCOUNT != 0
             AND Xact_state() != 0
            BEGIN
                ROLLBACK WORK
            END

          IF @ErrorMessage IS NULL
            BEGIN
                SET @ErrorMessage = 'Error!! Stored Procedure ' + @@SERVERNAME
                                    + '.' + Db_name() + '.' + @ProcedureName
                                    + ' Returned the following error :- '
                                    + Cast(Error_message() AS VARCHAR(1000))
                                    + ' Line Number :- '
                                    + Cast(Error_line()AS VARCHAR(1000))
            END

          RAISERROR(@ErrorMessage,16,1)

          RETURN( -1 )
      END CATCH

      RETURN( 0 )
  END