Error converting data type varchar to bigint

[747576]

Hi,
I am trying to convert values from the ROWID column in a table to a comma delimited list, like:

2,4,6,8,10

I am using a function, which used the ROW_NUMBER() function and keep getting the error:

Error converting data type varchar to bigint

Here is the code:


CREATE TABLE TEMPTABLE([HOLEID] [varchar] (20)
,[SAMPLEID] [varchar] (20)
)
GO
INSERT INTO TEMPTABLE(HOLEID,SAMPLEID) VALUES ('TEST0001','EX00010')
INSERT INTO TEMPTABLE(HOLEID,SAMPLEID) VALUES ('TEST0001','EX00009')
INSERT INTO TEMPTABLE(HOLEID,SAMPLEID) VALUES ('TEST0001','EX00008')
INSERT INTO TEMPTABLE(HOLEID,SAMPLEID) VALUES ('TEST0001','EX00007')
INSERT INTO TEMPTABLE(HOLEID,SAMPLEID) VALUES ('TEST0001','EX00006')
INSERT INTO TEMPTABLE(HOLEID,SAMPLEID) VALUES ('TEST0001','EX00005')
INSERT INTO TEMPTABLE(HOLEID,SAMPLEID) VALUES ('TEST0001','EX00004')
INSERT INTO TEMPTABLE(HOLEID,SAMPLEID) VALUES ('TEST0001','EX00003')
INSERT INTO TEMPTABLE(HOLEID,SAMPLEID) VALUES ('TEST0001','EX00002')
INSERT INTO TEMPTABLE(HOLEID,SAMPLEID) VALUES ('TEST0001','EX00001')

INSERT INTO TEMPTABLE(HOLEID,SAMPLEID) VALUES ('TEST0002','EX00010')
INSERT INTO TEMPTABLE(HOLEID,SAMPLEID) VALUES ('TEST0002','EX00009')
INSERT INTO TEMPTABLE(HOLEID,SAMPLEID) VALUES ('TEST0002','EX00008')
INSERT INTO TEMPTABLE(HOLEID,SAMPLEID) VALUES ('TEST0002','EX00007')
INSERT INTO TEMPTABLE(HOLEID,SAMPLEID) VALUES ('TEST0002','EX00006')
INSERT INTO TEMPTABLE(HOLEID,SAMPLEID) VALUES ('TEST0002','EX00005')
INSERT INTO TEMPTABLE(HOLEID,SAMPLEID) VALUES ('TEST0002','EX00004')
INSERT INTO TEMPTABLE(HOLEID,SAMPLEID) VALUES ('TEST0002','EX00003')
INSERT INTO TEMPTABLE(HOLEID,SAMPLEID) VALUES ('TEST0002','EX00002')
INSERT INTO TEMPTABLE(HOLEID,SAMPLEID) VALUES ('TEST0002','EX00001')

GO



Create Function GetRowidList2 (@FilterData varchar(20))
Returns VarChar(8000)
As
Begin
Declare @Result VarChar(8000)
Select @Result = IsNull(@Result + ',', '') + ROWID
From (SELECT * FROM(
SELECT 'FDUP' AS DUPLICATENO, SAMPLEID, ROW_NUMBER() OVER(ORDER BY SAMPLEID DESC) AS [ROWID] FROM TEMPTABLE WHERE HOLEID = @FilterData
)t1
WHERE ROWID%2=0)a
Where ROWID Is Not NULL
Order By ROWID
Return @Result
End

Select dbo.GetRowidList2('TEST0001')
From TEMPTABLE


Thank you

 

[bborissov]

I would define @result to varchar(max), just in case
Also you should CAST the ROWID to varchar

Create Function GetRowidList2  (@FilterData varchar(20))
Returns VarChar(max)
As
Begin
Declare @Result VarChar(max)
Select @Result = IsNull(@Result + ',', '') + [COLOR=red][b]CAST(ROWID as varchar(50))[/b][/color]
       From   (SELECT * FROM(
                      SELECT 'FDUP' AS DUPLICATENO,
                              SAMPLEID,
                              ROW_NUMBER() 
                              OVER(ORDER BY SAMPLEID DESC) AS [ROWID] 
                       FROM TEMPTABLE 
                       WHERE HOLEID = @FilterData)t1
               WHERE ROWID%2=0)a
Where  ROWID Is Not NULL   
Order By ROWID
Return @Result
End

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Yes, do the explicit conversion of RowID in the query.