I created a very simple table in Data Mart to store ETL diagnostic information:
CREATE TABLE [internal].[etl_status]
(
[property_name] VARCHAR(100) NOT NULL PRIMARY KEY,
[property_value] VARCHAR(MAX) NULL
)
Then I created a stored procedure to handle insertions/modifies into this table. Besides convenience, I had to do this because Microsoft are yet to add T-SQL statement activities to Azure data factory.
Problem is, I can't figure how to make the store procedure accept T-SQL function as parameters. In fact I believe it might not be possible. So here is my little workaround:
CREATE PROCEDURE [internal].[sp_set_etl_status]
@propertyName VARCHAR(100)
,@propertyValue VARCHAR(MAX)
AS
DECLARE @query AS VARCHAR(MAX)
-- Case statement to handle functions passed as propertyValue parameter
-- NOT IDEAL, see solution by Scott Hodgin below
SET @propertyValue = CASE @propertyValue
WHEN 'GETDATE()' THEN CONVERT(VARCHAR(MAX),CONVERT(DATETIME2(2), GETUTCDATE()))
ELSE @propertyValue
END
SET @query = '
BEGIN
IF NOT EXISTS ( SELECT * FROM [internal].[etl_status]
WHERE [property_name] =''' + @propertyName + ''')
BEGIN
INSERT INTO [internal].[etl_status]
VALUES (''' + @propertyName + ''', NULL)
END
END
UPDATE [internal].[etl_status]
SET [property_value] = ''' + @propertyValue+ '''
WHERE [property_name] = ''' + @propertyName + '''
;'
EXEC(@query)
This works as expected. But with the current solution I would need to have a CASE statement for each function I would like to handle.
EDIT: The suggestion below by Scott Hodgin is an acceptable solution. However I added a default value of FALSE
to @propertyValueIsFunction
as follows: @propertyValueIsFunction BIT = FALSE
.
Best Answer
If it's possible to add an additional parameter to your stored procedure (
PropertyValueIsFunction bit
), you could test for that condition and useREPLACE
to strip out the quotes. Here's an example:Assuming (notice the extra parameter value of 1 to indicate function):
exec [dbo].[sp_set_etl_status] 'admission_last_import_start_dttm', 'GETDATE()',1
the resulting query generated would look like this:
When you print the
@query
text, you'll see that the quotes are stripped from the function being passed in.You may have to play around with different scenarios, but this was my first thought to solve the original problem.