SSRS casting a parameter to decimal multi select

Question

Im trying to cast a parameter in SSRS to a decimal. I have a in clause since its multi select. I can select 1 and it runs fine however if i select more than 1 it will say

"Incorrect syntax near the keyword 'as'."

I am casting my parameter in my where clause in my query statement.

WHERE LOAD_NO IN (CAST(@Load as DECIMAL))

I am confused as to why it would bring back the syntax error if I select more than one from list.

Thanks

Answer 1

I am confused as to why it would bring back the syntax error if I select more than one from list.

Short answer

Because WHERE LOAD_NO IN (CAST(1,2,N as DECIMAL)) is not a valid T-SQL statement.

---

Long answer

When you use a multi-value parameter in a query, reporting services will generate different queries if your parameter contains 1 value, or multiple values.

Let's simplify your example to the following query:

SELECT * FROM TABLE WHERE LOAD_NO IN (@Load)

With only one value, the query will have the following format:

exec sp_executesql N'SELECT * FROM TABLE WHERE LOAD_NO IN (@Load)', N'@Load int', @Load=<YourValue>

It's a query with a parameter: @Load.

Now, with multiple values, the query will become

exec sp_executesql N'SELECT * FROM TABLE WHERE LOAD_NO IN (<YourValue1>, <YourValue2>,<YourValueN>)'

The @Load parameter has been replaced by the list of values.

So now my advise will be to rethink the design of your query and treat @Load as a list of values.

We cannot provide you the best solution because it really depends on the data and only you have all the details but I could still throw some ideas.

On the top of my head I could think of:

• Cast LOAD_NO instead, but the execution plan may loose the benefits of indexes if any.
• In most cases, using a IF EXISTS when possible instead of IN.
• Use a subquery.

Do not hesitate to run a SQL Server Profiler to see the generated query if you have other issues.

Answer 2

I'm not sure what your data looks like, so I'm not sure if these options would help, but here's a couple suggestions:

1. Try putting the CAST on LOAD_NO instead:

   WHERE CAST(LOAD_NO AS VARCHAR) IN (@Load)

2. Create a splitString function like the accepted post here (T-SQL split string) and access it in your WHERE clause:

   WHERE LOAD_NO IN (SELECT CAST(val AS DECIMAL) FROM dbo.splitString(@Load, ','))