SQL Select Query - Conversion Error

Question

Hi I am trying below query. Where output of ProjectID is 1,2,3. I am trying to fetch data using ProjectID from other table.

  DECLARE @ProjectID varchar(100)
 DECLARE @ImpactIntakeID varchar(100)

set @ProjectID =
(SELECT STUFF ((SELECT ',' + CAST(Proj_ID AS VARCHAR) FROM PROJECT
 WHERE YEAR(Enter_Dt) < (YEAR(GETDATE-()-7)) FOR XML PATH('')), 1, 1, '') 
AS Enter_Date)

print @ProjectID

set @ImpactIntakeID = 
(SELECT STUFF ((SELECT ',' + CAST([IMPCT_INTAKE_ID] AS VARCHAR) FROM [IMPCT_INTAKE_REQ]
 WHERE [CNSDR_PROJ_ID] in (@ProjectID) FOR XML PATH('')), 1, 1, '') 
 AS ImpactIntakeID)

print @ImpactIntakeID

I am getting conversion error. Conversion failed when converting the varchar value '1,2,3' to data type int. I am not sure where i am missing conversion. I also tried below..

 set @ImpactIntakeID = 
(SELECT STUFF ((SELECT ',' + CAST([IMPCT_INTAKE_ID] AS VARCHAR) FROM [IMPCT_INTAKE_REQ]
WHERE [CNSDR_PROJ_ID] in (''+@ProjectID+'') FOR XML PATH('')), 1, 1, '') 
AS ImpactIntakeID)

and

  set @ImpactIntakeID = 
 (SELECT STUFF ((SELECT ',' + CAST([IMPCT_INTAKE_ID] AS VARCHAR) FROM [IMPCT_INTAKE_REQ]
 WHERE CAST([CNSDR_PROJ_ID] AS VARCHAR) in (@ProjectID) FOR XML PATH('')), 1, 1, '') 
 AS ImpactIntakeID)

Can anyone please help where i am missing conversion...

Saharsh Shah · Accepted Answer · 2013-12-24T06:45:50.380

1

Try this:

SET @ImpactIntakeID = 
(SELECT STUFF ((SELECT ',' + CAST([IMPCT_INTAKE_ID] AS VARCHAR) FROM [IMPCT_INTAKE_REQ]
 WHERE [CNSDR_PROJ_ID] IN (SELECT Proj_ID FROM PROJECT WHERE YEAR(Enter_Dt) < (YEAR(GETDATE()-7))) FOR XML PATH('')), 1, 1, '') 
 AS ImpactIntakeID)

OR

SET @ImpactIntakeID = 
(SELECT STUFF ((SELECT ',' + CAST([IMPCT_INTAKE_ID] AS VARCHAR) FROM [IMPCT_INTAKE_REQ]
WHERE ',' + @ProjectID + ',' LIKE '%,' + CONVERT(VARCHAR(20), [CNSDR_PROJ_ID]) + ',%' FOR XML PATH('')), 1, 1, '') 
AS ImpactIntakeID)

edited Dec 24 '13 at 06:45

answered Dec 24 '13 at 06:08

Saharsh Shah

28,687
8
48
83

@saravanakumarr Check my updated answer. I had removed `-` from `GETDATE-()` and changed it to `GETDATE()`. It will work – Saharsh Shah Dec 24 '13 at 06:19
Thanks Ji...It is working...But i dont know why my approach causing conversion problem though i am assigning results in a variable & using it in a where in condition... – sk7730 Dec 24 '13 at 06:35
@saravanakumarr You're most welcome. In IN clause you have to pass all the values with same datatype. But here you are passing string data and comparing with integer data. – Saharsh Shah Dec 24 '13 at 06:47

score 0 · Answer 2 · answered Dec 24 '13 at 06:14

Use a Table-Valued User-Defined Function to convert comma separated string @ProjectID to records in a table . Then use this table in your Where clause

WHERE [CNSDR_PROJ_ID] in (SELECT projID from @ResultTable)

Table variable @ResultTable contains project ids in a column projID of type int which is populated using the userdefined function as given in the link.

SQL Select Query - Conversion Error

2 Answers2