cannot cast nvarchar to numeric error

What I have tried:

1. Casted all the select columns to nvarchar

2. the ssrs report parameter is “text” and the stored procedure parameters are numeric but casted as nvarchar

3. Some of the data has null rows would that cause this error?

SQL (obfuscated of course)

ALTER PROCEDURE SSRS_GetSomething

@ListOfIds as nvarchar(500)

BEGIN

SET NOCOUNT ON;

—Check the string for multiple premiseid’s and parse the string to convert from premise to numeric

DECLARE @result as nvarchar(500)

select TRY_CAST(id as nvarchar) as ‘ID1’,TRY_CAST(id2 as nvarchar) as ‘ID2’,streetaddress as ‘StreetAddress’,TRY_CAST([PossibleYear] as nvarchar) as ‘Year’,TRY_CAST(test2 as nvarchar) as ‘test2’,TRY_CAST(year3 as nvarchar) as ‘Year3’,TRY_CAST(numeric8 as nvarchar) as ‘NumericColumn’,TRY_CAST(numeric9 as nvarchar) as ‘numeric9’,TRY_CAST(numeric10 as nvarchar) as ‘Numeric10’,TRY_CAST(numeric11 as nvarchar) as ‘numeric11’,TRY_CAST(numeric12 as nvarchar) as ‘Numeric12’

from table1 b

order by b.numeric1 desc,b.numeric2 , b.numeric3;

END

Error:

An error has occurred during report processing. (rsProcessingAborted)
Cannot read the next data row for the dataset datasource1. (rsErrorReadingNextDataRow)
Error converting data type nvarchar to numeric.

I’m getting the following error no matter what I do. I’ve casted all the columns to nvarchar in the sql query and the input parameter is text in SSRS and nvarchar(500) in the stored procedure. If I run the query/stored procedure it works fine. but if I run it in SSMS I get this error no matter what I do? What I have tried:1. Casted all the select columns to nvarchar2. the ssrs report parameter is “text” and the stored procedure parameters are numeric but casted as nvarchar3. Some of the data has null rows would that cause this error? SQL (obfuscated of course)ALTER PROCEDURE SSRS_GetSomething @ListOfIds as nvarchar(500)ASBEGINSET NOCOUNT ON; —Check the string for multiple premiseid’s and parse the string to convert from premise to numericDECLARE @result as nvarchar(500) select TRY_CAST(id as nvarchar) as ‘ID1’,TRY_CAST(id2 as nvarchar) as ‘ID2’,streetaddress as ‘StreetAddress’,TRY_CAST([PossibleYear] as nvarchar) as ‘Year’,TRY_CAST(test2 as nvarchar) as ‘test2’,TRY_CAST(year3 as nvarchar) as ‘Year3’,TRY_CAST(numeric8 as nvarchar) as ‘NumericColumn’,TRY_CAST(numeric9 as nvarchar) as ‘numeric9’,TRY_CAST(numeric10 as nvarchar) as ‘Numeric10’,TRY_CAST(numeric11 as nvarchar) as ‘numeric11’,TRY_CAST(numeric12 as nvarchar) as ‘Numeric12’ from table1 border by b.numeric1 desc,b.numeric2 , b.numeric3; ENDGO Error:An error has occurred during report processing. (rsProcessingAborted)Cannot read the next data row for the dataset datasource1. (rsErrorReadingNextDataRow)Error converting data type nvarchar to numeric. Read More

Cart

Cart