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

[Paul Hartland]

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
>
>


-- 
Paul Hartland
paul.hartland at googlemail.com