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

[John W. Colby]

This doesn't work.

cteTestIf as
(
     if @Select = 'St'
     begin
         SELECT * from AZData;
     end
),

returns compile error

Msg 156, Level 15, State 1, Procedure usp_MoveData, Line 34
Incorrect syntax near the keyword 'if'.

John W. Colby

On 7/24/2015 1:43 PM, Paul Hartland wrote:
> The if's work different in sql server, you would need something like
>
> If @selector = 'ST'
> Begin
> Select xyz
> End
>
> Else if @selector = 'MSA'
> Begin
> Select abc
> End
>
> Paul
> On 24 Jul 2015 18:39, "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
>
>