sql server mseq: I am having a little problem creating this query.

I have a stored procedure that accepts 4 parameters.
One of the parameters tells the query which column to sort on.

Select
Case
When @ReportParam = 'Col1' Then Col1
When @ReportParam = 'Col2' Then Col2
End as ReportOrder,
Col3
From Table

Now I also have to add the zero records.
To do this I am using a union statement to add the zero records.
This approach worked fine when I had 1 column to sort on but now with this
parameter telling me which column to sort on I don't know how to union to get
the zero records.

Select
Case
When @ReportParam = 'Col1' Then Col1
When @ReportParam = 'Col2' Then Col2
End as ReportOrder,
Col3
From Table

Union

Select Case
When @ReportParam = 'Col1' Then Col1
When @ReportParam = 'Col2' Then Col2
End as ReportOrder,
Col3
From Table
where ReportOrderCol not in(Select (Col1, Col2, Col3) from Table)

Is there a way to use a case or If statement to choose which sql statement
to use
when using a union statement.

Let me know if there is any info that i left out.

[quoted text, click to view]

Hi Fez,

I'm not sure if I understand what you are trying to achieve. A better
way to explain would be to post CREATE TABLE statements (including
constraints and properties), INSERT statements with some sample data and
expected output. Include the code from your last attempt and the results
of running that code as well.

See www.aspfaq.com/5006 for more details.

Best, Hugo
--