Store proc body with select and update

Question

We have store proc in azure sql db which is having select and update statement,

with select we don't have any issue but while updating if we are updating less then 14 rows then it's working fine but if we have more then 14 rows then it is not updating.

SELECT DISTINCT

	@source_table_paths AS src_tbl_path, 

	@source_tables as source_table_list,

	@BATCH_TS AS BATCH_TS,

	SRC_STORAGE_ACCT_NM, 

	SRC_CONTAINER_NM,

	SRC_LS_NAME, 

	TGT_DB_NM, 

	TGT_TBL_PATH, 

	TGT_TBL_NM, 

	TGT_STORAGE_ACCT_NM, 

	TGT_CONTAINER_NM, 

	TGT_LS_NAME, 

	LOAD_FREQ, 

	LOAD_TYPE, 

	ACTIVE_IND,

	SYN_WORKSPACE_NM, 

	SYN_NOTEBOOK_NM, 

	SYN_NOTEBOOK_PATH,

	@ERROR_MSG As ERROR_MSG

	FROM dbo.BATCH_CONTROL_MAPPING WHERE 

	TGT_TBL_NM = @TGT_TBL_NM 

UPDATE BCE

	SET CURATED_PIPELINE_ID =  @CURATED_PIPELINE_ID   

	FROM DBO.BATCH_CONTROL_EXECUTION BCE

	INNER JOIN (

		SELECT SRC_TBL_NM, MIN(BATCH_TS) AS MIN_BATCH_TS

		FROM DBO.BATCH_CONTROL_EXECUTION

		WHERE PROCESS_STATUS = 'R'

		  AND upper(TGT_TBL_NM) = UPPER(@TGT_TBL_NM)

		GROUP BY SRC_TBL_NM

	) AS MinBatchTS ON BCE.SRC_TBL_NM = MinBatchTS.SRC_TBL_NM AND BCE.BATCH_TS = MinBatchTS.MIN_BATCH_TS

	WHERE BCE.PROCESS_STATUS = 'R'

	AND upper(BCE.TGT_TBL_NM) = UPPER(@TGT_TBL_NM)

	--AND BCE.BATCH_TS = MinBatchTS.MIN_BATCH_TS

when i am moving update first then select then it's working fine

below is the query:

--update

	**UPDATE BCE**

	**SET CURATED_PIPELINE_ID =  @CURATED_PIPELINE_ID**   

	**FROM DBO.BATCH_CONTROL_EXECUTION BCE**

	**INNER JOIN (**

		**SELECT SRC_TBL_NM, MIN(BATCH_TS) AS MIN_BATCH_TS**

		**FROM DBO.BATCH_CONTROL_EXECUTION**

		**WHERE PROCESS_STATUS = 'R'**

		  **AND upper(TGT_TBL_NM) = UPPER(@TGT_TBL_NM)**

		**GROUP BY SRC_TBL_NM**

	**) AS MinBatchTS ON BCE.SRC_TBL_NM = MinBatchTS.SRC_TBL_NM AND BCE.BATCH_TS = MinBatchTS.MIN_BATCH_TS**

	**WHERE BCE.PROCESS_STATUS = 'R'**

	**AND upper(BCE.TGT_TBL_NM) = UPPER(@TGT_TBL_NM)**

	**--AND BCE.BATCH_TS = MinBatchTS.MIN_BATCH_TS**

	--Select the source tables with respective target table.

	**SELECT DISTINCT**

	**@source_table_paths AS src_tbl_path,** 

	**@source_tables as source_table_list,**

	**@BATCH_TS AS BATCH_TS,**

	**SRC_STORAGE_ACCT_NM,** 

	**SRC_CONTAINER_NM,**

	**SRC_LS_NAME,** 

	**TGT_DB_NM,** 

	**TGT_TBL_PATH,** 

	**TGT_TBL_NM,** 

	**TGT_STORAGE_ACCT_NM,** 

	**TGT_CONTAINER_NM,** 

	**TGT_LS_NAME,** 

	**LOAD_FREQ,** 

	**LOAD_TYPE,** 

	**ACTIVE_IND,**

	**SYN_WORKSPACE_NM,** 

	**SYN_NOTEBOOK_NM,** 

	**SYN_NOTEBOOK_PATH,**

	**@ERROR_MSG As ERROR_MSG**

	**FROM dbo.BATCH_CONTROL_MAPPING WHERE** 

	**TGT_TBL_NM = @TGT_TBL_NM** 

Answer 1

you don't show the code that calls the proc, but most likely its not reading the results. the SP needs to return all the rows to the caller before it can begin the update. if the caller does not read the rows or cancels, then the update is not done.

Answer 2

Hi @Nikhil Somani

Have you verified if exists deadlocks or locks that prevent the update process.

Besides, if you're executing the stored procedure from an application, it might also be caused by the timeout issue. Try increasing the timeout value.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

Your SQL code ist unreadable formatted and when don't have your database to test anything.

Please post a repro code.

if we have more then 14 rows then it is not updating.

Why do you think so; there is no-where a TOP clause to limit the effected rows?