CODEWITHSUNDEEP
azure-data-factory
Rohit Kulkarni
Rohit Kulkarni

Reputation: 63

Parameter value in table

I have hard coded values in one of the table

enter image description here

And I am passing this column as a parameter and try to delete some values .The below is mentioned querie:

DELETE FROM [S4].[@{pipeline().parameters.DESTINATION_TABLE_NAME}] WHERE ERDAT >='@{item().LastLoadDate}' OR @{pipeline().parameters.WHERE_SQL} >='@{item().LastLoadDate}

I am getting error :

ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Incorrect syntax near '>'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Incorrect syntax near '>'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=102,Class=15,ErrorCode=-2146232060,State=1,Errors=[{Class=15,Number=102,State=1,Message=Incorrect syntax near '>'.,},],'

Please advise.

Regards Rohit

Upvotes: 0

Views: 155

Answers (1)

Saideep Arikontham
Saideep Arikontham

Reputation: 6114

  • As shown in the image, the where_sql column also has null values. When you try to use one of these values in the query, the error occurs.
  • The following is a demonstration where I have a table with where_sql column. I used a look up activity to get these values.

enter image description here

  • Now, I used for each to iterate through this result. Inside for each I have used a script activity with following script:
delete  from demo where idt>='2023-04-01'  or @{item().where_sql}>='2023-04-01'
  • When I execute this script, it gives the required result. But when there is null value, the query gives the same error.

enter image description here

  • You can see what query is getting executed in this scenario. The following is an image for reference.

enter image description here

  • So, try filtering out the rows where where_sql column value is null thus leaving you with the required column names to query with.

Upvotes: 0

Related Questions