My requirement is to pass input parameter with some Integers into DB to fetch the data. But in the front end, I'm checking if we didn't select any input data (Report type of application), NULL should pass else all the data or selected data should pass.
WHERE
a.DateTime BETWEEN '2018-04-12 00:00:00' AND '2018-04-12 23:59:59'
AND ISNULL('0000441183344450,0000447769267501,0000447789917187',CallNumber) = CallNUmber
AND ISNULL(CAST('1,2,3' AS INT), ID) = ID
In the above query ISNULL(CAST('1,2,3' AS INT), ID) = ID is causing an error:
Conversion failed when converting the varchar value '1,2,3' to data type int
I know its very generic error but my requirement is to pass the selected Integer values or all values if the user didn't select any values in the input.
In the database, ID has an int datatype and if used with quote values (and ISNULL('121','122',AgentSkillTargetID)=AgentSkillTargetID) I'm getting an error
The isnull function requires 2 argument(s)
In the same query
ISNULL('0000441183344450,0000447769267501,0000447789917187',CallNumber) = CallNUmber
is working, since the datatype of CallNumber is varchar.
The isnull function is a t-sql function that can only accept two parameters. For returning the first non-null value from a larger list of parameters, use the ansi-complient coalesce function instead. Some people might claim you should never use isnull, since it's not ansi complient. Personally, I don't think that it's a good enough reason. I think you should use the best tool available.
For more information, read the Comparing COALESCE and ISNULL section of the coalesce doc page.