Stored Procedure with a Cursor

Hi all,

Please can anyone help me with this. I'm trying to write a SQL Server stored procedure using as cursor but am getting a bit stuck on some syntax. I've tried online books but can't find the right examples. Please can anyone spot what is wrong with this? Thanks!

Error messages are:
Server: Msg 156, Level 15, State 1, Procedure VacancyReport, Line 5
Incorrect syntax near the keyword 'declare'.
Server: Msg 156, Level 15, State 1, Procedure VacancyReport, Line 59
Incorrect syntax near the keyword 'End'.
Server: Msg 170, Level 15, State 1, Procedure VacancyReport, Line 65
Line 65: Incorrect syntax near 'Loop'.


-- ==================================================
-- Create procedure to get data for vacancies report
-- ==================================================
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'VacancyReport'
AND type = 'P')
DROP PROCEDURE VacancyReport
GO
CREATE PROCEDURE VacancyReport

--procedure variables
declare @indep_wte numeric,
@sal_wte numeric,
@total_wte numeric,
@ave_wte numeric,
@temp_prac_code varchar(8),
@initials varchar(10),
@name varchar(100),
@prac_code varchar(8),
@lhg varchar(15),
@pms varchar(3),
@num_indep numeric,
@num_sal_gp numeric,
@vacancies_wte numeric,
@list_size numeric,
@wte numeric,
@type varchar(15)

declare VacancyCursor cursor for
select t1.initials, t1.name, t1.prac_code, t1.lhg, t1.pms, t1.num_indep, t1.num_sal_gp,
t1.vacancies_wte, t1.list_size, t2.wte, t2.type
from gp_practice t1 inner join gpprac_link t2 on t1.prac_code = t2.prac_code
order by t1.prac_code
open VacancyCursor
fetch next from VacancyCursor into
@initials,
@name,
@prac_code,
@lhg,
@pms,
@num_indep,
@num_sal_gp,
@vacancies_wte,
@list_size,
@wte,
@type
While @@fetch_status = 0
Begin Transaction
set @temp_prac_code = @prac_code
If @type = 'INDEPENDENT'
set @indep_wte = @indep_wte + @wte
Else

set @sal_wte = @sal_wte + @wte
End If
set @total_wte = @sal_wte + @indep_wte
set @ave_wte = @total_wte / @list_size

Loop
Close VacancyCursor
Deallocate VacancyCursor

Well, is there a good reason to drop the procedure then create it? Using your code as an example, I found the source of the first error. My SQL Server is telling me that: Server: Msg 111, Level 15, State 1, Line 7
'CREATE PROCEDURE' must be the first statement in a query batch.

It apparently does not like the GO before your create procedure line. That is why you are getting that error, but if you remove the word go, you will get the error I mention above. Maybe there is another way? Can you create the procedure and call it from your program instead of re-creating it each time?

On the declaration error, that is likeley caused because each variable declaration must be preceeded with the "DECLARE" keyword. You only have it at the beginning. Try removing the commas and begin each line with "DECLARE".

The 'Incorrect syntax near end' is likely because you seem to be missing the "END" keyword. I see an "END IF" but no end for your BEGIN.

Also, I don't think you need the loop.

I hope this helps. Let me know if you need an example of a cursor, I can post it for you.

Ok, I took out the drop proc (that's something leftover from my Oracle days).

I also checked out the 'declare' situation and it's fine to only have 1 declare.

I still have the problem with 'End If'. It doesn't like it. Is the syntax different? I tried ENDIF but it didn't like that either. It's not to do with the end transaction being missing though.

Also, I thought I would need a Loop statement to match the While statement above. How else do I loop through the recordset?
Any ideas anyone? (the version below works but only because the If is commented out!)

CREATE PROCEDURE dbo.VacancyReport as

--procedure variables
declare @indep_wte numeric (9),
@sal_wte numeric (9),
@total_wte numeric (9),
@ave_wte numeric (9),
@temp_prac_code varchar(8),
@initials varchar(10),
@name varchar(100),
@prac_code varchar(8),
@lhg varchar(15),
@pms varchar(3),
@num_indep numeric (9),
@num_sal_gp numeric (9),
@vacancies_wte numeric (9),
@list_size numeric (9),
@wte numeric (9),
@type varchar(15)


--declare VacancyCursor cursor for

select t1.initials, t1.name, t1.prac_code, t1.lhg, t1.pms, t1.num_indep, t1.num_sal_gp,
t1.vacancies_wte, t1.list_size, t2.wte, t2.type
from gp_practice t1 inner join gpprac_link t2 on t1.prac_code = t2.prac_code
order by t1.prac_code

--open VacancyCursor

fetch next from VacancyCursor into
@initials,
@name,
@prac_code,
@lhg,
@pms,
@num_indep,
@num_sal_gp,
@vacancies_wte,
@list_size,
@wte,
@type

While @@fetch_status = 0

Begin Transaction

set @temp_prac_code = @prac_code

-- If @type = 'INDEPENDENT'

set @indep_wte = @indep_wte + @wte

-- Else

set @sal_wte = @sal_wte + @wte

-- End If

set @total_wte = @sal_wte + @indep_wte
set @ave_wte = @total_wte / @list_size


--Loop
--End Transaction

Close VacancyCursor
Deallocate VacancyCursor
GO

Good to know about the DECLARE... I thought that it had to be done for each one. YAY! a time saving step. Thanks! As for the cursor, I posted an example in another forum, I will copy it here. Are you using T-SQL by the way? That is what I am using, so you may have to do some minor translation if you are using another version.



This example is not exactly the same as what you are doing obviously, but the while statement above is looped using the "FETCH NEXT FROM...INTO ..." This will execute the cursor until the fetch status <> 0. You have that statement at the beginning of your cursor. Adding it to the end will execute the loop.

As for the IF statement, IF in SQL is a little different. Start by saying IF but instead of THEN use BEGIN. Use Begin before telling SQL what it should do if your statement is true. Then when the BEGIN portion is done close with "END". If you need an ELSE block it goes after the "END". Try that and see if it works with the IF statement. If that still does not work, try running just the if statement in the Query Analyzer alone and fiddle with it until it works.

Thanks.

My problem was the begin and end parts. I did not realise I should put them after each if. It now works fine.

Thanks again!

No problem Paula! I am glad I could help