Cursor issue in an SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Cursor issue in an SP

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-08-15 : 07:42:38

Dheeraj writes "Hi There

I was trying to mark all the duplicates in a table where the table name will be concatenated with the Month i.e 07 and year 2005 so the table name should be TableName072005. When i pass month & year as a parameters to the Stored procedure you can see in the below script when i try to check the syntax its
giving me an error this is what it says,

Server: Msg 156, Level 15, State 1, Procedure MarkDuplicates, Line 8
Incorrect syntax near the keyword 'Set'.
Server: Msg 137, Level 15, State 1, Procedure MarkDuplicates, Line 15
Must declare the variable '@Sql1'.

I would appriciate if someone could shed some light where i'm wrong. Thank you for your time.

Script Starts Here,

CREATE Procedure MarkDuplicates
@StrMonth Varchar(2),
@StrYear varchar(4)
As
DECLARE @Dups int, @fld1 varchar(100), @fld2 varchar(100), @fld3
varchar(100), @fld4 VARCHAR(100),@Sql1 Varchar(500),@Sql2 Varchar(2500)
Set @Sql1 = 'NCMAST'+Ltrim(@StrMonth)+Ltrim(@StrYear)
DECLARE Dups CURSOR FOR
Set @Sql2 = 'SELECT [Check No],[Date], Amount,Company, COUNT(*) - 1 AS
Duplicates FROM '+@Sql1+' GROUP BY [Check No],[Date],Amount,Company HAVING
COUNT(*) > 1'
Exec(@Sql2)
OPEN Dups WHILE(1=1)
BEGIN
FETCH NEXT FROM Dups INTO @fld1, @fld2, @fld3, @fld4, @Dups
IF @@FETCH_STATUS < 0 BREAK
SET ROWCOUNT @Dups
Update @Sql1 Set Deleted = 1 Where
[Check No] = @fld1 And
[Date] = @fld2 And
Amount = @fld3 And
Company = @fld4
SET ROWCOUNT 0
END
DEALLOCATE Dups
GO

Script Ends Here"

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2005-08-15 : 11:01:48

Several things I can see wrong.


CREATE Procedure MarkDuplicates
@StrMonth Varchar(2),
@StrYear varchar(4)
As
DECLARE @Dups int, @fld1 varchar(100), @fld2 varchar(100), @fld3 
varchar(100), @fld4 VARCHAR(100),@Sql1 Varchar(500),@Sql2 Varchar(2500)
Set @Sql1 = 'NCMAST'+Ltrim(@StrMonth)+Ltrim(@StrYear) 
DECLARE Dups CURSOR FOR
Set @Sql2 = 'SELECT [Check No],[Date], Amount,Company, COUNT(*) - 1 AS Duplicates FROM '+@Sql1+' GROUP BY [Check No],[Date],Amount,Company HAVING COUNT(*) > 1' 
Exec(@Sql2)
OPEN Dups WHILE(1=1)
BEGIN
FETCH NEXT FROM Dups INTO @fld1, @fld2, @fld3, @fld4, @Dups
IF @@FETCH_STATUS < 0 BREAK
SET ROWCOUNT @Dups
Update @Sql1 Set Deleted = 1 Where 
[Check No] = @fld1 And 
[Date] = @fld2 And 
Amount = @fld3 And 
Company = @fld4
SET ROWCOUNT 0
END
DEALLOCATE Dups
GO

On line 8 you are declaring CURSOR FOR, but not specifying a SELECT statement. I assume you want to use the SELECT in the @SQL2 variable, but this isn't allowed AFAIK.

Then you have an EXEC(@Sql2) which doesn't seem to be doing anything. It'll only return a result set into nowhere, unless you plan to otherwise process it.

You cannot have the OPEN Dups and the WHILE 1=1 on the same line. Split it into two separate lines.

If @Sql1 is a variable (you declared it as varchar(500), and not a temp table, you cannot use the UPDATE statement on it. In about line 15, you are attempting to do this.

Please put a CLOSE Dups before the DEALLOCATE Dups.

Sarah Berger MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources