Solved

Help with SQL syntax please..

Posted on 2016-08-16
10
115 Views
Last Modified: 2016-08-26
Hi,

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

sql agent
Please help!
0
Comment
Question by:SmashAndGrab
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41757753
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.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41757754
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

0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 41757756
move that code to a new procedure and then just call that new procedure
0
 

Author Comment

by:SmashAndGrab
ID: 41757811
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.
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 41757820
use google: transact sql loop through records
you'll probably follow this item:
https://support.microsoft.com/en-us/kb/111401
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:SmashAndGrab
ID: 41757826
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

0
 

Author Comment

by:SmashAndGrab
ID: 41757829
db cursor
0
 
LVL 46

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 41757879
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

0
 
LVL 12

Assisted Solution

by:Nakul Vachhrajani
Nakul Vachhrajani earned 125 total points
ID: 41757883
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
1
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 125 total points
ID: 41757958
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
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now