locked
Modifying the SQL stored procedure by calling parameters from Data factory RRS feed

  • Question

  • Hi,

    It will be more or less question for both SQl and Data factory part. I have the below stored procedure which update the Watermark table with watermark value by matching the corresponding table name.

    ALTER PROCEDURE [dbo].[usp_UpdateWatermark] 
     @tableName nvarchar(500)
     
    AS 
    BEGIN
    
     DECLARE
     /* ============= Variables ============= */
      @watermarkValue  nvarchar(MAX)
      
     
     /* Determine latest Watermark value from input table */
     SELECT
      @watermarkValue = MAX([LOAD_DATE])
     FROM dbo.CustomerInput
     
     /* Update Watermark table */
     UPDATE dbo.WatermarkTable
     SET  WatermarkValue  = @watermarkValue 
     WHERE TableName = @tableName 
    END

    Here instead of using the exact table name as CustomerInput, I would like to pass the parameter '@tablename'. The tablename parameter value is feeded from Data factory. If I replace the dbo.CustomerInput with @tablename, i get the error. Can any expert help me in modifying it.

    Why I Need this:

    • I have multiple tables and not just CustomerInput and all have LOAD_DATE as column.
    • I would like to store the max(LOAD_DATE) value for each table in Watermark table
    • My Watermark table has columns TableName where we have all the table names like CustomerInput, CustomerPending etc and WatermarkValue column which will have value of max(LOAD_DATE) for each corresponding table.

    Also note: above Stored procedure is working fine for now but it populates the Watermarkvalue for each row with MAX(LOAD_DATE) of CustomerInput table.

    Thanks


    zzzSharePoint


    Friday, February 28, 2020 12:51 PM

All replies

  • Hi there,

    Here's an example of a Stored Procedure that takes in the table name as a parameter: 

    CREATE PROCEDURE xyz @TableName NVARCHAR(128) AS BEGIN SET NOCOUNT ON; DECLARE @Sql NVARCHAR(MAX); SET @Sql = N'SELECT TOP 10 * INTO #Temp_Table_One FROM ' + QUOTENAME(@TableName) + N' SELECT * FROM #Temp_Table_One ' EXECUTE sp_executesql @Sql END

    Ref - https://stackoverflow.com/a/22105223/10653466

    You can now send parameters to the Stored Procedure from your ADF pipeline as shown below :

    

    Hope this helps.

    Monday, March 2, 2020 11:45 AM
  • Hi Chirag,

    I fear thats not working for me.. below is my modified SP and now I get this error: 

    Msg 208, Level 16, State 6, Procedure usp_UpdateWatermark, Line 2 [Batch Start Line 5]
    Invalid object name 'dbo.usp_UpdateWatermark'.

    
    
    GO
    /* Create Stored Procedure */
    ALTER PROCEDURE [dbo].[usp_UpdateWatermark] 
     @tableName nvarchar(500)
     
    AS 
    BEGIN
    
     DECLARE
     /* ============= Variables ============= */
      @watermarkValue  nvarchar(MAX)
    
      DECLARE
     /* ============= Variables ============= */
      @tabName  nvarchar(MAX)
      Declare @ParmDefinition nvarchar(MAX);
      SET @ParmDefinition = N'@watermarkValue int OUTPUT';
      Set @tabName = N'SELECT @watermarkValue = MAX([LOAD_DATE]) FROM '+ QUOTENAME(@tableName)
    
     Execute sp_executesql @tabName, @ParmDefinition,@watermarkValue=@watermarkValue
      
     
     /* Determine latest Watermark value from input table 
     SELECT
      @watermarkValue = MAX([LOAD_DATE])
     FROM dbo.Fish_Transfer_Staging
     */
     /* Update Watermark table */
     UPDATE dbo.WatermarkTable
     SET  WatermarkValue  = @watermarkValue 
     WHERE TableName = @tableName 
    END

    Can you please elaborate what wrong is happening here


    zzzSharePoint

    Monday, March 2, 2020 1:16 PM
  • Hi there,

    Sorry for the delayed response.

    Do you mean you get the error when you run the above script or that you get the error when you attempt to execute the stored procedure ?

    If you get the error when you run the above script, it is because ALTER PROCEDURE only works if the stored procedure already exists.  If it doesn't exist, you need CREATE PROCEDURE.  So if that is your problem, just change ALTER to CREATE.

    If you create the same stored procedure in the Master Database and then try to alter it, it should work.

    Hope this helps.

    Ref - https://social.technet.microsoft.com/Forums/en-US/a216c0da-dc3d-4d96-a965-56c449bc3983/stored-procedure-error-msg-208-level-16-state-6-procedure-procdepartmentselect-line-24?forum=transactsql

    Thursday, March 5, 2020 6:47 AM