CODEWITHSUNDEEP
sqlsql-server-2008cursor
Callum
Callum

Reputation: 3

SQL Cursor Syntax Issue

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

Upvotes: 0

Views: 3535

Answers (2)

SWeko
SWeko

Reputation: 30882

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

Upvotes: 1

Rhumborl
Rhumborl

Reputation: 16609

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

Upvotes: 0

Related Questions