Help with SQL syntax please..

SmashAndGrab
SmashAndGrab used Ask the Experts™
on
Hi,

I have an SQL Agent job that needs to do the following..

sql agent
Please help!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
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.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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

Open in new window

Geert GOracle dba
Top Expert 2009

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

Author

Commented:
Thanks guys.

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

Referenced it here..

SQLJOB2.JPG

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

Open 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.
Geert GOracle dba
Top Expert 2009

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

Author

Commented:
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 

Open in new window

Author

Commented:
db cursor
IT Engineer
Distinguished Expert 2017
Commented:
You have an infinite loop. Inside the WHILE you'll need to fetch the next record or otherwise it will keep processing the same record forever.
Also, you have a BEGIN but not an END block. Anyway, you won't need it for a Stored Procedure so I've removed it:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Alter PROCEDURE [dbo].[FF_spOptio_Concat_Checker]

AS

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
       FETCH NEXT FROM db_cursor INTO @Code
END   

CLOSE db_cursor   
DEALLOCATE db_cursor 

GO

Open in new window

Nakul VachhrajaniTechnical Architect, Capgemini India
Commented:
That's because you don't have the "END" for the stored procedure itself.

ALTER PROCEDURE...
AS
BEGIN
....
WHERE @@FETCH_STATUS = 0
   BEGIN
   END
...
CLOSE db_cursor
DEALLOCATE db_cursor

END -- This "END" appears to be missing
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Or concat an execution string instead of looping:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[FF_spOptio_Concat_Checker]

AS

BEGIN

DECLARE @Code                  int
DECLARE @sql            varchar(max)

SELECT @sql = (
    SELECT 'EXEC dbo.Optio_Concat ' + CAST(Code AS varchar(10)) + ';'
    FROM dbo.FF_TALLY_TABLE
    WHERE Optio_Concat IS NULL
    ORDER BY Code
    FOR XML PATH('')
    )

--PRINT @sql
EXEC(@sql)

END

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial