Help with SQL syntax please..

try write a stored procedure that within itself it will doing the relevant looping to call another stored procedure. so that in your SQL Agent job, it only requires to call one single stored procedure.

hope this give you some idea to do it.

You need to declare variable @code and store the Code on it. I'm guessing is INT but if not then change to the correct data type:

DECLARE @Code INT

SELECT @Code = Code 
FROM FF_TALLY_TABLE
WHERE Optio_Concat IS NULL

EXEC sp_name @Code

Select allOpen in new window

move that code to a new procedure and then just call that new procedure

Thanks guys.

So, I've taken your advice and created another stored procedure.

Referenced it here..



Now the bit I am not sure about..

 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Alter PROCEDURE [dbo].[FF_spOptio_Concat_Checker]

AS

BEGIN

DECLARE @Code			int


Select Code FROM  FF_TALLY_TABLE WHERE(Optio_Concat IS NULL)


//Now execute the original stored procedure
EXEC Optio_Concat @Code

END

Select allOpen in new window

The query could bring back more than 1 record, so I need to execute the stored procedure (EXEC Optio_Concat @Code), potentially, multiple times.

Hope I've made sense.

use google: transact sql loop through records
you'll probably follow this item:
https://support.microsoft.com/en-us/kb/111401

I found this..

Thought it had worked and it didn't :(

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Alter PROCEDURE [dbo].[FF_spOptio_Concat_Checker]

AS

BEGIN

DECLARE @Code			int

DECLARE db_cursor CURSOR FOR Select Code FROM FF_TALLY_TABLE WHERE(Optio_Concat IS NULL)

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @Code

WHILE @@FETCH_STATUS = 0   
BEGIN   
       EXEC Optio_Concat @Code
END   

CLOSE db_cursor   
DEALLOCATE db_cursor 

Select allOpen in new window