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

[Paul Hartland]

I am out at the moment so cant do any testing but does it also error if you
put select * from cteTestIf below the whole cte statement ?
On 25 Jul 2015 07:26, "John W. Colby" <jwcolby at gmail.com> wrote:

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