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

[John W. Colby]

I haven't yet.  I was counting on you Paul!  ;)

John W. Colby

On 7/27/2015 11:41 PM, Paul Hartland wrote:
> sorry for the delay but hmmmmm it appears you can't have an IF statement in
> a CTE, I had never tried it before....Just done a test setting up some
> CTE's then trying to select one using an IF afterwards and doesn't seem to
> work, sorry can't be of more help at the moment, I don't use CTE's very
> much at all I tend to just use temp tables etc, but would be interested to
> know how you resolved this one.
>
> Paul
>
> On 25 July 2015 at 14:00, John W. Colby <jwcolby at gmail.com> wrote:
>
>> Yes.  You must do what you are suggesting, however I am already doing that
>> to select another CTE.  IOW as long as you do that final select, it can
>> select from any of the CTEs in the stored procedure.
>>
>> --select * from cteDiffDataAll
>> --select * from cteDiffDataAllWithRowNum
>> --select Top (1000) * from cteLastMove
>> --select * from cteGeoDistance
>> --SELECT COUNT(PKNew) AS Cnt, StNew, StOld FROM cteLastMove GROUP BY
>> StNew, StOld
>> --SELECT COUNT(PKNew) AS Cnt, StNew, DistanceCode FROM cteLastMoveDistance
>> GROUP BY StNew, DistanceCode
>>
>> Select * from cteTestIf
>>
>>
>>
>> John W. Colby
>>
>>
>> On 7/25/2015 8:52 AM, Paul Hartland wrote:
>>
>>> 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
>>>>
>>>>
>>>>   _______________________________________________
>>> 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
>>
>>
>