Error message on Cursor - 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

Transact-SQL (2000)

Error message on Cursor

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-08-22 : 17:19:39

I am keep gettign the converting error meessge when I run below cursor.
Server: Msg 245, Level 16, State 1, Line 58
Syntax error converting the nvarchar value 'SET ROWCOUNT 1 DELETE IndTest WHERE Site = 'AA009' AND ID = '30587262828||1' AND SALE = '3050276286' AND TDESC = 'AAAAAAAAAAAAAAAAAAAAA' AND TRANS = '' to a column of data type int.

-- declare variables
DECLARE @iErrorVar int,
@iCount int,
@chSite char(5),
@vchID varchar(254),
@vchSale varchar(50),
@vchTDESC varchar(255),
@iTrans int,
@iTType int,
@iTType2 int,
@nvchCommand nvarchar(4000),
@chCount char(3)

-- set initial environment
SET ROWCOUNT 0
SET NOCOUNT ON

-- Build cursor to find duplicated information

DECLARE DelDupe CURSOR FOR
SELECT COUNT(*) AS Amount,Site,"ID",SALE,TDESC,TRANS,TTYPE,TTYPE2
FROM F_POS_LOAD
where SDate >= '06/01/2005' and SDate <= '06/30/2005'
GROUP BY Site,"ID",SALE,TDESC,TRANS,TTYPE,TTYPE2
HAVING COUNT(*) > 1

OPEN DelDupe
print @icount
FETCH NEXT FROM DelDupe INTO @iCount,
@chSite,
@vchID,
@vchSale,
@vchTDESC,
@iTrans,
@iTType,
@iTType2

WHILE (@@fetch_status = 0)

BEGIN
-- Calculate number of rows to delete for each grouping by subtracting
-- 1 from the total count for a given group.
print @icount
SELECT @iCount = @iCount - 1
SELECT @chCount = CONVERT(char(3),@iCount)
-- now build the rowcount and delete statements.
--select char(39)
SELECT @nvchCommand = N'SET ROWCOUNT ' + @chCount +
' DELETE IndTest ' +
' WHERE Site = ' + CHAR(39) + @chSite + CHAR(39) +
' AND ID = ' + CHAR (39) + @vchID + CHAR(39) +
' AND SALE = ' + CHAR (39) + @vchSale + CHAR(39) +
--' AND RxScriptNo = ' + CHAR (39) + @vchRxScriptNo + CHAR(39) +
' AND TDESC = ' + CHAR (39) + @vchTDESC + CHAR(39) +
' AND TRANS = ' + CHAR (39) + @iTrans + CHAR(39) +
' AND TTYPE = ' + CHAR (39) + @iTType + CHAR(39) +
' AND TTYPE2 = ' + CHAR(39) + @iTType2 + CHAR(39)

-- print the statement. For your viewing pleasure only.

PRINT @nvchCommand
-- execute the statement.

--EXEC sp_executesql @nvchCommand

SELECT @iErrorVar = @@Error
IF @iErrorVar <> 0
BEGIN
RETURN
END

FETCH NEXT FROM DelDupe INTO @iCount,
@chSite,
@vchID,
@vchSale,
@vchTDESC,
@iTrans,
@iTType,
@iTType2

END
CLOSE DelDupe
DEALLOCATE DelDupe
RETURN

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-08-22 : 20:15:07

Need to convert some of your variables to character:

SELECT @nvchCommand = N'SET ROWCOUNT ' + CONVERT(varchar, @chCount) +
' DELETE IndTest ' +
' WHERE Site = ' + CHAR(39) + @chSite + CHAR(39) +
' AND ID = ' + CHAR (39) + @vchID + CHAR(39) +
' AND SALE = ' + CHAR (39) + @vchSale + CHAR(39) +
--' AND RxScriptNo = ' + CHAR (39) + @vchRxScriptNo + CHAR(39) +
' AND TDESC = ' + CHAR (39) + @vchTDESC + CHAR(39) +
' AND TRANS = ' + CHAR (39) + CONVERT(varchar, @iTrans) + CHAR(39) +
' AND TTYPE = ' + CHAR (39) + CONVERT(varchar, @iTType) + CHAR(39) +
' AND TTYPE2 = ' + CHAR(39) + CONVERT(varchar, @iTType2) + CHAR(39)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources