SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic column selection


Dynamic column selection

Author
Message
duncanwill
duncanwill
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 19
I have seen posts about this concept and replies basically say that what I want is wrong... but here goes anyway!

I have a dataset with say 15 data columns (plus other info columns ie.. date, username etc) and I want to select data for one data column only based on user specified input. So something like....

select vchrCustomer, vchrFirst_name, vchrSurname , mycolumnvalue
from thetable
where
mycolumnvalue <> 0

The where clause ensures I get only relevant info for users request and the select clause means I can have a "one report does all" approach on the front end (asp page)

Comments are:

1) I didn't design the table - I am stuck with it!
2) I don't want to use dynamic SQL
3) Do I really have to do this 15 times in an unpleasant "if then" or "select case" type statment?

I hope this makes sense - it really seems a simple idea to me but I'm willing to be told otherwise!
Jason Selburg
Jason Selburg
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3733 Visits: 4110
duncan.williams (12/3/2007)


1) I didn't design the table - I am stuck with it!
2) I don't want to use dynamic SQL
3) Do I really have to do this 15 times in an unpleasant "if then" or "select case" type statment?


Yes, you will need to use a CASE statement or Dynamic SQL. BigGrin

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
duncanwill
duncanwill
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 19
So.... given the real world example where my proc is actually considerably longer than the simple select shown, I am going to have to replicate the code 15 times?.. or use dynamic SQL which is loses the precompiled advantage of being a proc in the first place?

Angry
Jason Selburg
Jason Selburg
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3733 Visits: 4110
NO.... you won't have to replicate it.

Post it here and I'll show you....

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
duncanwill
duncanwill
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 19
OK Thanks!

Here's a more realistic snippet (you'll have to work around the temp table!):

SELECT cc.vchrCustomer, tintWeekNo,dteWeekEndSubmit, MYCOLUMN , s.vchrFirst_name + ' ' + s.vchrSurname, cc.vchrFile_number
FROM tbl_bpm_customer_collection cc
INNER JOIN #staff s
ON
s.vchrFile_Number = cc.vchrFile_number
WHERE
cc.tintWeekNo
BETWEEN
@intFrom AND @intTo
AND
cc.vchrYear = @intYear
and
MYCOLUMN <> 0

MYCOLUMN is the variable column....
Jason Selburg
Jason Selburg
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3733 Visits: 4110
Ok, what are the conditions for MYCOLUMN and what should/will be returned...

I mean, is it something like if a "1" is passed the first column is returned, "2" the second etc... or "someValue" then "a certain column" and "another value" then "a different column"


In other words, what determines which column is returned. I need a list of the what's and which's in that question.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
duncanwill
duncanwill
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 19
Yeah sorry... a parameter will be passed into the proc giving the column name (or a look up value if required - as it hasn't been written yet!).

So lets assume we have the parameter defined as follows and that this contains the column name:

@vchrColumn varchar(50)

Some of the column names are:

intStartUp
intSwitcher
intExisting
intIncome
intSwitchOpen
intSwitchCust
intSavings
intOverdraft
intOverdraftFee
intMoney
intEstimated
intArrangement
intValuation
intAdditional
intBLRI
intCORI
intEBI
intFPC
Jason Selburg
Jason Selburg
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3733 Visits: 4110
Provided all of your return columns are of the same dataType, and I assume from their names they are. this will return the column depending on the parameter value passed in. And from your original post, if that value is 0 then it's not returned.

The column returned will always be named MYCOLUMN. That will make it easy to write the front end.

NOTE: I could have used a CASE in the WHERE clause as well, but I wanted to show you another way to accomplish it.


SELECT
cc.vchrCustomer
,tintWeekNo
,dteWeekEndSubmit
,MYCOLUMN =
CASE @vchrColumn
WHEN 'intStartUp' THEN intStartUp
WHEN 'intSwitcher' THEN intSwitcher
WHEN 'intExisting' THEN intExisting
WHEN 'intIncome' THEN intIncome
WHEN 'intSwitchOpen' THEN intSwitchOpen
WHEN 'intSwitchCust' THEN intSwitchCust
WHEN 'intSavings' THEN intSavings
WHEN 'intOverdraft' THEN intOverdraft
WHEN 'intOverdraftFee' THEN intOverdraftFee
WHEN 'intMoney' THEN intMoney
WHEN 'intEstimated' THEN intEstimated
WHEN 'intArrangement' THEN intArrangement
WHEN 'intValuation' THEN intValuation
WHEN 'intAdditional' THEN intAdditional
WHEN 'intBLRI' THEN intBLRI
WHEN 'intCORI' THEN intCORI
WHEN 'intEBI' THEN intEBI
WHEN 'intFPC' THEN intFPC
END
,s.vchrFirst_name + ' ' + s.vchrSurname
,cc.vchrFile_number
FROM
tbl_bpm_customer_collection cc
INNER JOIN #staff s
ON s.vchrFile_Number = cc.vchrFile_number
WHERE
cc.tintWeekNo BETWEEN @intFrom AND @intTo
AND cc.vchrYear = @intYear
AND (
(@vchrColumn = 'intStartUp' AND intStartUp <> 0)
OR
(@vchrColumn = 'intSwitcher' AND intSwitcher <> 0)
OR
(@vchrColumn = 'intExisting' AND intExisting <> 0)
OR
(@vchrColumn = 'intIncome' AND intIncome <> 0)
OR
(@vchrColumn = 'intSwitchOpen' AND intSwitchOpen <> 0)
OR
(@vchrColumn = 'intSwitchCust' AND intSwitchCust <> 0)
OR
(@vchrColumn = 'intSavings' AND intSavings <> 0)
OR
(@vchrColumn = 'intOverdraft' AND intOverdraft <> 0)
OR
(@vchrColumn = 'intOverdraftFee' AND intOverdraftFee <> 0)
OR
(@vchrColumn = 'intMoney' AND intMoney <> 0)
OR
(@vchrColumn = 'intEstimated' AND intEstimated <> 0)
OR
(@vchrColumn = 'intArrangement' AND intArrangement <> 0)
OR
(@vchrColumn = 'intValuation' AND intValuation <> 0)
OR
(@vchrColumn = 'intAdditional' AND intAdditional <> 0)
OR
(@vchrColumn = 'intBLRI' AND intBLRI <> 0)
OR
(@vchrColumn = 'intCORI' AND intCORI <> 0)
OR
(@vchrColumn = 'intEBI' AND intEBI <> 0)
OR
(@vchrColumn = 'intFPC' AND intFPC <> 0)
)




______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
duncanwill
duncanwill
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 19
Great thanks.

It aint pretty but I guess it is what it is! A lot better than 15 x the code.

Cheers again.
Jason Selburg
Jason Selburg
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3733 Visits: 4110
Hey, I think it's pretty ... LOL BigGrin

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search