Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Dynamic column selection Expand / Collapse
Author
Message
Posted Monday, December 3, 2007 5:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 28, 2009 3:36 AM
Points: 8, 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!
Post #428655
Posted Monday, December 3, 2007 5:10 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:50 PM
Points: 2,717, Visits: 3,854
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. :D


______________________________________________________________________

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
Post #428657
Posted Monday, December 3, 2007 5:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 28, 2009 3:36 AM
Points: 8, 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?



Post #428658
Posted Monday, December 3, 2007 5:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:50 PM
Points: 2,717, Visits: 3,854
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
Post #428662
Posted Monday, December 3, 2007 5:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 28, 2009 3:36 AM
Points: 8, 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....
Post #428665
Posted Monday, December 3, 2007 5:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:50 PM
Points: 2,717, Visits: 3,854
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
Post #428666
Posted Monday, December 3, 2007 5:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 28, 2009 3:36 AM
Points: 8, 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



Post #428672
Posted Monday, December 3, 2007 5:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:50 PM
Points: 2,717, Visits: 3,854
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
Post #428678
Posted Monday, December 3, 2007 5:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 28, 2009 3:36 AM
Points: 8, 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.
Post #428682
Posted Monday, December 3, 2007 5:53 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:50 PM
Points: 2,717, Visits: 3,854
Hey, I think it's pretty ... LOL :D

______________________________________________________________________

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
Post #428683
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse