Conditionally show columns in SQL

[Dean Savovic]

Almost positive that this is impossible without using dynamic sql!

--
Dean Savovic
[url]www.teched.hr[/url]


"Pierre Dippenaar" <pierre@sentinal.co.za> wrote in message
news:d1f00c2a.0307010018.46c481a8@posting.google.c om...

> I would like to show a column in a SQL statement only if a certain
> condition is true (all within a stored procedure - without using
> Dynamic SQL):
>
> Create Procedure spAuthors
> @ShowFirstName smallint
> As
>
> SELECT au_lname, au_fname (Only Show Firstname if @ShowFirstName = 1)
> FROM Authors
>
> Is this possible without using Dynamic SQL and the EXEC statement? I
> tried using the CASE statement but this still dislays a column for
> FirstName even though the values are not displayed.

[Jacco Schalkwijk]

Create Procedure spAuthors
@ShowFirstName smallint
AS
SET NOCOUNT ON

IF @ShowFirstName = 1
SELECT au_lname, au_fname
FROM Authors
ELSE
SELECT au_lname
FROM Authors

I think you shouldn't go too far with making the output of your stored
procedures dynamic, it will only confuse in the end. If your rowssets are
reasonably small the performance improvement of not sending the extra data
over the network is neglegible and it is far easier handled in the client.

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


"Pierre Dippenaar" <pierre@sentinal.co.za> wrote in message
news:d1f00c2a.0307010018.46c481a8@posting.google.c om...

> I would like to show a column in a SQL statement only if a certain
> condition is true (all within a stored procedure - without using
> Dynamic SQL):
>
> Create Procedure spAuthors
> @ShowFirstName smallint
> As
>
> SELECT au_lname, au_fname (Only Show Firstname if @ShowFirstName = 1)
> FROM Authors
>
> Is this possible without using Dynamic SQL and the EXEC statement? I
> tried using the CASE statement but this still dislays a column for
> FirstName even though the values are not displayed.