SQL Azure - Create External Data Source: Location as a Parameter?

Question

I'm attempting to create a SQL stored procedure that will create an External Data Source. The location is to be a parameter passed into the procedure (e.g. an azure storage account container). I can't get the Location variable to be accepted (as in the code snippet below).

DECLARE @Location varchar(max)
SET @Location = 'https://somestorageaccount.blob.core.windows.net/uploads'

PRINT @Location
--DROP EXTERNAL DATA SOURCE uploads

CREATE EXTERNAL DATA SOURCE uploads
WITH
(
    TYPE = BLOB_STORAGE,
    LOCATION = @Location,
    CREDENTIAL = azurecred
);

The error msg is:

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '@Location'.

It works if the url with single quotes replaces the variable. I.e.

CREATE EXTERNAL DATA SOURCE uploads
WITH
(
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://somestorageaccount.blob.core.windows.net/uploads'
    CREDENTIAL = azurecred
);

Having experimented I cannot get this to work. Is it possible?

Answer 1

You cannot use variables in DDL statements. Try using dynamic sql - form the string for your DDL and execute it using sp_executesql stored procedure

ALTER PROCEDURE CETFromNewLocation    AS

BEGIN

DECLARE @location varchar(100)

SET @location = 'data/2015/2015831'

DECLARE @CreateExternalTableString varchar(100)

SET @CreateExternalTableString = 
                                    'Create External TABLE stg_tbl (
                                                  [DateId] int NULL
                                    )
                                    WITH (LOCATION = ' + @location + ',                                      
                                             DATA_SOURCE = my_external_source,
                                             FILE_FORMAT = my_external_file_format,
                                             REJECT_TYPE = VALUE,
                                             REJECT_VALUE = 0
                                    )'

EXEC sp_executesql @CreateExternalTableString

END