> I simply want to do this in a stored procedure:
>
> If @myValue = 1
> Select Field1 From myTable
> Else
> If @myValue = 2
> Select Field1, Field2 From myTable
>
> In query analyzer I get the right select statement, but in dotnet or
> access I get only the first select statement, unless I change it to
> this:
>
> If @myValue = 1
> Select Field1 From myTable
> Return
> Else
> If @myValue = 2
> Select Field1, Field2 From myTable
> Return
>
> Now adding the returns made this work, but I am not sure why. Any solid
> way to do this?

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns991F612D8D68FYazorman@127.0.0.1...
> Derek Hart (derekmhart@yahoo.com) writes:
>> I simply want to do this in a stored procedure:
>>
>> If @myValue = 1
>> Select Field1 From myTable
>> Else
>> If @myValue = 2
>> Select Field1, Field2 From myTable
>>
>> In query analyzer I get the right select statement, but in dotnet or
>> access I get only the first select statement, unless I change it to
>> this:
>>
>> If @myValue = 1
>> Select Field1 From myTable
>> Return
>> Else
>> If @myValue = 2
>> Select Field1, Field2 From myTable
>> Return
>>
>> Now adding the returns made this work, but I am not sure why. Any solid
>> way to do this?
>
> If I understand this correctly, the rows returned are the same in both
> cases, only that you return more columns when @myValue = 2.
>
> My suspicion is that the client API for some reason do not get the
> differences, and always display the smaller set of columns. You could
> verify this by using different WHERE clauses in the two IF branches.
> If you then see different values depending on @myValue, my theory is
> right.
>
> And if not... it would help to see the actual code, and not the least the
> code you have in Access or .Net.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

"David W" <spivey@nospam.post.com> wrote in message
news:%23E1%23OOOiHHA.4600@TK2MSFTNGP05.phx.gbl...
> The second way shouldn't even compile without BEGIN-END after the if and
> before the else.
>
>>> I simply want to do this in a stored procedure:
>>>>
>>>> If @myValue = 1
>>>> Select Field1 From myTable
>>>> Else
>>>> If @myValue = 2
>>>> Select Field1, Field2 From myTable
>>>>
>>>> In query analyzer I get the right select statement, but in dotnet or
>>>> access I get only the first select statement, unless I change it to
>>>> this:
>>>>
>>>> If @myValue = 1
>>>> Select Field1 From myTable
>>>> Return
>>>> Else
>>>> If @myValue = 2
>>>> Select Field1, Field2 From myTable
>>>> Return
>>>>
>
>

>> I simply want to do this in a stored procedure:
>>>
>>> If @myValue = 1
>>> Select Field1 From myTable
>>> Else
>>> If @myValue = 2
>>> Select Field1, Field2 From myTable
>>>
>>> In query analyzer I get the right select statement, but in dotnet or
>>> access I get only the first select statement, unless I change it to
>>> this:
>>>
>>> If @myValue = 1
>>> Select Field1 From myTable
>>> Return
>>> Else
>>> If @myValue = 2
>>> Select Field1, Field2 From myTable
>>> Return
>>>

> The system returns the first select statement even if @myValue=2, unless I
> put in the returns. Not sure why SQL Server parses this procedure
> differently with the returns. Is the 2nd way I wrote the code done
> properly?

On Thu, 26 Apr 2007 17:57:55 -0700, Derek Hart wrote:

>I simply want to do this in a stored procedure:
>
>If @myValue = 1
> Select Field1 From myTable
>Else
>If @myValue = 2
> Select Field1, Field2 From myTable
>
>In query analyzer I get the right select statement, but in dotnet or access
>I get only the first select statement, unless I change it to this:
>
>If @myValue = 1
> Select Field1 From myTable
> Return
>Else
>If @myValue = 2
> Select Field1, Field2 From myTable
> Return
>
>Now adding the returns made this work, but I am not sure why. Any solid way
>to do this?