[dba-SQLServer] TSQL IF in a non-standard way

[David McAfee]

SELECT
 CASE
     WHEN @Selector = 'ST' THEN XYZ
     WHEN @Selector = 'MSA' THEN ABC
     ELSE '???'
 END AS StrSelected
FROM Some TABLE

You can use Case like you would use an IIF() in Access (in a join, where
clause)...

On Fri, Jul 24, 2015 at 10:38 AM, John W. Colby <jwcolby at gmail.com> wrote:

>
> I am writing a stored procedure which uses "cascading" CTEs to grab a raw
> data set, and then transform that raw data set.  For example I need "All
> the addressed in a table" joined to "all the addresses in another table".
> Simple enough.
>
> Then I do transforms looking for distances between the addresses in the
> two tables.  Also simple enough.
>
> But Now sometimes I just need all addresses, sometimes I need only
> "specific counties" or "specific MSA numbers" or "specific states". I have
> to pull data from a CSV, a county list, or a state list, or an MSA list.
>
> What I am thinking is use an IF or switch to return a result set from a
> select.
>
> If @Selector = 'ST' Select XYZ else if @Selector = 'MSA' Select ABC
>
> Etc.
>
> This doesn't seem to work, or at least I can't find examples of this kind
> of use.  IF seems to be used in TSQL to return specific values in fields
> inside of a select statement.
>
> Any suggestions for how to implement this?
>
> --
> John W. Colby
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>