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

[John W. Colby]

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