Loop through data to execute SP

Transact-SQL
1

Trying to loop through some GUID's here. My SP works fine on it's own (EXEC sp_Send_Claim_To_Archive '146e9b9f-ad18-4303-aafb-0307083a3b03' for example). My fn_Split works fine in my other SP's so it isn't the problem either. This actually runs successfully but the archive table isn't being populated. If I replace by variable with '146e9b9f-ad18-4303-aafb-0307083a3b03' that works. Seems I can't have a variable in quotes.

Suggestions?

DECLARE @GUIDs VARCHAR(MAX) = '146e9b9f-ad18-4303-aafb-0307083a3b03'

IF LEN(@GUIDs) > 1
	BEGIN
		DECLARE @singleGUID AS VARCHAR(50) 
		
		DECLARE toUpdateGUID CURSOR FOR

		SELECT VALUE FROM dbo.fn_Split(@GUIDs,',')

		OPEN toUpdateGUID
	
		FETCH NEXT FROM toUpdateGUID
			INTO @singleGUID
		WHILE @@FETCH_STATUS = 0
			BEGIN
				EXEC sp_Send_Claim_To_Archive '@singleGUID'
				
				FETCH NEXT FROM toUpdateGUID
				INTO @singleGUID
			END
	
		CLOSE toUpdateGUID
		DEALLOCATE toUpdateGUID
	END
2

Not in open SQL, no. Why do you want to quote the variable name?

3

I don't know any better ...I tried to adapt some code from another developer for my purposes. I'm sending a long string of GUIDs delimited by a comma to @GUIDs. I need to run sp_Send_CLaim_To_Archive for each GUID in the string.

4

I see now ... no reason to have the variable in quotes. Below is sample code that works but only on the first GUID in the string.

USE [Home Care]

declare @GUIDs AS varchar(50) = '146e9b9f-ad18-4303-aafb-0307083a3b03, c6d92bde-b9dc-430e-9d9e-335211c0e1ed, 4f4baf97-be66-4ad6-a07c-6141da24f818'

IF LEN(@GUIDs) > 1
		BEGIN
			DECLARE @singleGUID AS VARCHAR(50)
			
			DECLARE toUpdateGUID CURSOR FOR

			SELECT VALUE FROM dbo.fn_Split(@GUIDs,',')

			OPEN toUpdateGUID
		
			FETCH NEXT FROM toUpdateGUID
				INTO @singleGUID
			WHILE @@FETCH_STATUS = 0
				BEGIN
					EXEC sp_Send_Claim_To_Archive @singleGUID
					
					FETCH NEXT FROM toUpdateGUID
					INTO @singleGUID
				END
		
			CLOSE toUpdateGUID
			DEALLOCATE toUpdateGUID
		END
5

Can you confirm that this proc is set up to handle a list of GUIDs?

6

I found my error ... there shouldn't be any spaces between the comma and the next GUID. Removed the spaces and the statement runs as it should.

Thank you for your help.

7

Also where I declare GUIDs I had it set to varchar(50) so it was only getting the first GUID. Set it to MAX so as the rest of the string would come into play.

8

The next thing you should do is post your fn_Split function. Credits to Navy Beans says it probably has a While Loop or Recursive CTE in it.