using conditional statement in stored prcodure to build select statement

[web developer]

hi
I need to write a stored procedure that takes input parameters,and
according to these parameters the retrieved fields in a select
statement are chosen.
what i need to know is how to make the fields of the select statement
conditional,taking in consideration that it is more than one field
added
for example
SQLStmt="select"
if param1 then
SQLStmt=SQLStmt+ field1
end if
if param2 then
SQLStmt=SQLStmt+ field2
end if

[RSMEINER]

>hi

I need to write a stored procedure that takes input parameters,and
according to these parameters the retrieved fields in a select
statement are chosen.
what i need to know is how to make the fields of the select statement
conditional,taking in consideration that it is more than one field
added
for example
SQLStmt="select"
if param1 then
SQLStmt=SQLStmt+ field1
end if
if param2 then
SQLStmt=SQLStmt+ field2
end if
.
.
.
.
SQLStmt=SQLStmt + " from Table1"
execute SQLStmt
any ideas about how to implement such a logic in stored procedures
using SQL Server 2000
thanks in adavance

Declare @SQLStmt VARCHAR(2048)

Set @SQLStmt = 'Select '

if param1 then
SET @SQLStmt = @SQLStmt + field1

if param2 then
SET @SQLStmt = @SQLStmt + field2

Set @SQLStmt = @SQLStmt + ' from Table1'

exec (@SQLStmt)

Randy
http://members.aol.com/rsmeiner

[Erland Sommarskog]

[posted and mailed, please reply in news]

web developer (o_*****@yahoo.com) writes:

I need to write a stored procedure that takes input parameters,and
according to these parameters the retrieved fields in a select
statement are chosen.
what i need to know is how to make the fields of the select statement
conditional,taking in consideration that it is more than one field
added
for example
SQLStmt="select"
if param1 then
SQLStmt=SQLStmt+ field1
end if
if param2 then
SQLStmt=SQLStmt+ field2
end if
.
.
.
.
SQLStmt=SQLStmt + " from Table1"
execute SQLStmt
any ideas about how to implement such a logic in stored procedures
using SQL Server 2000

I have a longer article on this topic on
http://www.sommarskog.se/dyn-search.html.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp