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

[Arthur Fuller]

I like both of the answers presented, but would push it one step further,
and instead of using a SELECT statement in each case I would create
separate SPs, each devoted to one of the cases, then EXEC the appropriate
one, passing a parameter. That's an issue of personal taste; I haven't
exhaustively benchmarked the differences. It's just my preference for what
I call "atomic" procedures, that do precisely one thing with known input
parameters.

Arthur

On Fri, Jul 24, 2015 at 1:50 PM, David McAfee <davidmcafee at gmail.com> wrote:

> 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
> >
> >
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


-- 
Arthur