If parm string = then get column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

If parm string = then get column

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2011-12-29 : 16:16:41

I'm looking for a way to exclude certain columns from a select.

@param = 'blue'

SELECT
col1,
col2,
col3

FROM myTable WHERE col4 = @param

Now, if @param = 'red'

Leave out col2

I don't want to write a bunch of if else statements because the query is quite long.

Suggestions?

thanks,

Zath

sql-programmers
Posting Yak Master

190 Posts

Posted - 2011-12-30 : 01:33:07

I Thing You can write Dynamic Query only using if statement. Like

@param = 'blue'
DECLARE @QUERY NVARCHAR(MAX)
@QUERY = ' SELECT col1, '

IF (@param<>'red')
@QUERY = @QUERY + 'col2, '

@QUERY = @QUERY + 'col3 '
@QUERY = @QUERY + 'FROM myTable WHERE col4 = ' + @param
sp_execute_sql @QUERY

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-12-30 : 07:04:12

Even better, return all the columns, and filter the results in your front end, thus eliminating the need for dynamic sql.
If we saw the ACTUAL select, perhaps we could suggest a better way (perhaps with CASE).

select col1, col2, case @param when 'blue' then col3 when 'red' then col4 end as 'Whatever'

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources