1

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.

  • Can you alter the stored procedure to accept an additional parameter (PropertyValueIsFunction bit that would indicate the propertyValue being passed in is actually a function. You could test for that condition and use REPLACE to strip out the quotes. – Scott Hodgin Jan 13 at 12:22
  • @ScottHodgin VERY interesting solution! What I did so far was to fix the CASE statement I did above, but it is cludgy. How would I use the replace statement in this case? Also can I add default values to a parameter, so if I don't specify it, it becomes FALSE by default? Also how would you test for it? – pmdci Jan 13 at 12:58
1

If it's possible to add an additional parameter to your stored procedure (PropertyValueIsFunction bit), you could test for that condition and use REPLACE to strip out the quotes. Here's an example:

CREATE PROCEDURE [dbo].[sp_set_etl_status]
    @propertyName VARCHAR(100)
   ,@propertyValue VARCHAR(MAX)
   ,@propertyValueIsFunction bit
AS

DECLARE @query AS VARCHAR(MAX)

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 + '''
;'
if @propertyValueIsFunction = 1
    set @query = replace(@query,'''' + @propertyValue + '''',@propertyValue) 
print @Query
--EXEC(@query)

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:

BEGIN
    IF NOT EXISTS ( SELECT * FROM [internal].[etl_status]
                        WHERE [property_name] ='admission_last_import_start_dttm')
    BEGIN
        INSERT INTO [internal].[etl_status]
        VALUES ('admission_last_import_start_dttm',  NULL)
    END
END

UPDATE [internal].[etl_status]
SET [property_value] = GETDATE()
WHERE [property_name] = 'admission_last_import_start_dttm'
;

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.

| improve this answer | |
  • The print statement does not work. The stored procedure no longer writes anything, although it does not return any errors. if I keep the EXEC instead of the PRINT, it works, but it returns an error. – pmdci Jan 13 at 13:19
  • 1
    What error does it return? The print statement was just so you could see the generated SQL. Is the generated SQL a valid command? I don't have your table definitions, so it's a bit difficult to reproduce your issue. – Scott Hodgin Jan 13 at 13:22
  • Perhaps you could update your question to include a minimal, reproducible example of the problem you're having with my proposed solution? – Scott Hodgin Jan 13 at 13:23
  • In looking at your example, I would pass in the full function command as the parameter instead of using a case expression. So, you'd pass in something like exec [dbo].[sp_set_etl_status] 'admission_last_import_start_dttm', 'CONVERT(VARCHAR(MAX),CONVERT(DATETIME2(2), GETUTCDATE()))',1 – Scott Hodgin Jan 13 at 13:28
  • 1
    So, you should not have any case expressions in the stored procedure and then you should be able to supply the full function command in the propertyValue parameter and use the simple replace I suggested to strip out the quotes. Please update your question to include the very latest attempt so we are both looking at the same thing. Also, please post the error messages. – Scott Hodgin Jan 13 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.