SQL Cursor Syntax Issue

Question

Im getting this error Msg 102, Level 15, State 1, Procedure CostTest_02, Line 43 Incorrect syntax near ';'.

With "GO" underlined in red I've been looking for the error for a while and I can't find! I'm not very experienced with cursors( this is the first one I've written) If anyone can spot it or any other errors I'd be very thankful

https://i.sstatic.net/JwB1w.jpg <----- picture is a lot more clear(recommended)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE CostTest_02
AS
BEGIN

DECLARE @Issue_id int;
DECLARE @Master_id int;
DECLARE @Issue_table varchar(255);
DECLARE @price real;
DECLARE @rowcount bigint;


DECLARE cost_cursor cursor FOR
SELECT [Issue Id], [Master Id], [Issue Table], [Price]
from Adhoc_datafix..[Issue Table]
FOR UPDATE OF ADHOC_DATAFIX..[Issue Cost];

OPEN cost_cursor;
FETCH NEXT FROM cost_cursor into @Issue_ID, @Master_id, @Issue_table, @Price

WHILE @@FETCH_STATUS = 0
BEGIN

Select count(*) from @Issue_Table
set @Rowcount = @@rowcount

UPDATE ADHOC_DATAFIX..[Issue Cost]
set  [Issue Id] = @Issue_ID ,
[Master Issue Id] = @Master_ID ,
[Row Count] = (Select count(*) from @Issue_Table), --@Rowcount,
[Cost] = CAST(@Rowcount * @price as money)

WHERE CURRENT OF cost_cursor;
FETCH NEXT FROM cost_cursor
END

close cost_cursor;

DEALLOCATE cost_cursor;
GO

SWeko · Accepted Answer · 2013-01-10 13:35:11Z

1

~~My guess is that the line FETCH NEXT FROM cost_cursor near the bottom of the script should be FETCH NEXT FROM cost_cursor into @Issue_ID, @Master_id, @Issue_table, @Price~~ .

Sorry, on second look this line:

Select count(*) from @Issue_Table

does not make sense since @Issue_table is not actually a table, it's a nvarchar.
In runtime, that is transferred to:

Select count(*) from 'Value of issue table field'

which, to put it simply, does not work. If you want to dynamically get the count from a table, take a look at this seminal article

edited Jan 10, 2013 at 13:35

answered Jan 10, 2013 at 13:29

SWeko

31k11 gold badges74 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

SWeko Over a year ago

About the fetches, they are both needed, since sql server cursors use something like a loop-and-a-half

Callum Over a year ago

@Issue_Table contains a string to a table that actually exists which is what I'm trying to select all from, I don't think this is dynamic SQL but I still need to figure out a way of doing this? Do you know how

SWeko Over a year ago

@Callum That is exactly what dynamic SQL is.

Callum Over a year ago

Would it work in this situation as the table name will be changing each time the cursor is looped?

SWeko Over a year ago

Yes, that is the exact problem that dynamic sql is solving.

|

Rhumborl · Accepted Answer · 2013-01-10 13:31:22Z

0

You do not have an END statement for your stored procedure. Just add that before GO

answered Jan 10, 2013 at 13:31

Rhumborl

16.7k4 gold badges41 silver badges47 bronze badges

3 Comments

Callum Over a year ago

Yes this helped a lot actually, but im still not sure about the two different type of FETCH that I'm using surely only one is needed?

Rhumborl Over a year ago

you need both fetches, but they should both be FETCH NEXT FROM cost_cursor into @Issue_ID, @Master_id, @Issue_table, @Price. The first fetch gets the first matched row and sets up the initial fetch_status, i.e. did it find something. After each loop, you call fetch again to try and get another row and update fetch_status.

Callum Over a year ago

Thanks a lot! Thats what I was thinking just needed confirmation

Collectives™ on Stack Overflow

SQL Cursor Syntax Issue

2 Answers 2

8 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related