Posted by GIS Analyst on 09/29/05 07:20

Hi to all

I wish to be able to have a standard select statement which has
additional fields added to it at run-time based on supplied
parameter(s).

ie
declare @theTest1 nvarchar(10)
set @theTest1='TRUE'

declare @theTest2 nvarchar(10)
set @theTest2='TRUE'

select
p_full_name
if @theTest1='TRUE'
BEGIN
other field1,
END
if @theTest2='TRUE'
BEGIN
other field2
END

from dbo.tbl_GIS_person
where record_id < 20


I do not wish to use an IF statement to test the parameter for a
condition and then repeat the entire select statement particularly as
it is a UNIONed query for three different statement

ie
declare @theTest1 nvarchar(10)
set @theTest1='TRUE'

declare @theTest2 nvarchar(10)
set @theTest2='TRUE'

if @theTest1='TRUE' AND @theTest2='TRUE'
BEGIN
select
p_full_name,
other field1,
other field2
from dbo.tbl_GIS_person
where record_id < 20
END

if @theTest1='TRUE' AND @theTest2='FALSE'
BEGIN
select
p_full_name,
other field1
from dbo.tbl_GIS_person
where record_id < 20
END
..
..
..
if @theTest<>'TRUE'
BEGIN
select
p_full_name
from dbo.tbl_GIS_person
where record_id < 20
END

Make sense? So the select is standard in the most part but with small
variations depending on the user's choice. I want to avoid risk of
breakage by having only one spot that the FROM, JOIN and WHERE
statements need to be defined.

The query will end up being used in an XML template query.

Any help would be much appreciated

Regards

GIS Analyst

[Back to original message]