<div>
try this query<br />
<br />
SELECT * FROM TableName <br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE (CASE WHEN ISNULL(@Specialty, '') = '' THEN 1 <br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;WHEN @Specialty IS NOT NULL AND Specialty IN (Select Value From dbo.fnParmsToList(@Special<wbr />ty))<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;ELSE 0) = 1
</div>


try this query

SELECT * FROM TableName
      WHERE (CASE WHEN ISNULL(@Specialty, '') = '' THEN 1
                    WHEN @Specialty IS NOT NULL AND Specialty IN (Select Value From dbo.fnParmsToList(@Specialty))
                    ELSE 0) = 1

<div>
<div class="content wysiwyg-content">
Hi Experts<br />
<br />
I have a select statement, where I pass in a parameter (@Specialty) this comes in as a comma separated list of values and I convert these to a table variable and use it like so.<br />
<br />
Where Specialty in (Select Value From dbo.fnParmsToList(@Special<wbr />ty)).<br />
<br />
What I want is to ignore this value if it is null, (or empty string I can force it to either value in the calling code)<br />
<br />
I usually use coalesce to return the first non null value, and this works great for single parameter values, but I cant get it to work with the above.<br />
<br />
So my question is how can I ignore the parameter @Specialty and return ALL values unless I specify the parameter.<br />
<br />
If I might just add, this is not the only value in the where clause, there are 5 others, where I use coalesce in 3.<br />
<br />
Andy
</div>
</div>


Hi Experts

I have a select statement, where I pass in a parameter (@Specialty) this comes in as a comma separated list of values and I convert these to a table variable and use it like so.

Where Specialty in (Select Value From dbo.fnParmsToList(@Specialty)).

What I want is to ignore this value if it is null, (or empty string I can force it to either value in the calling code)

I usually use coalesce to return the first non null value, and this works great for single parameter values, but I cant get it to work with the above.

So my question is how can I ignore the parameter @Specialty and return ALL values unless I specify the parameter.

If I might just add, this is not the only value in the where clause, there are 5 others, where I use coalesce in 3.

Andy

SQL How to ignore string parameter

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.