December 3, 2007 at 5:05 am
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!
December 3, 2007 at 5:10 am
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. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 3, 2007 at 5:12 am
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:
December 3, 2007 at 5:18 am
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[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 3, 2007 at 5:22 am
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....
December 3, 2007 at 5:28 am
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[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 3, 2007 at 5:34 am
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
December 3, 2007 at 5:45 am
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[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 3, 2007 at 5:49 am
Great thanks.
It aint pretty but I guess it is what it is! A lot better than 15 x the code.
Cheers again.
December 3, 2007 at 5:53 am
Hey, I think it's pretty ... LOL 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 3, 2007 at 4:23 pm
You may enclose whole nasty part in a view.
At least it will be hidden from your eyes. 🙂
[Code]
SELECT
cc.vchrCustomer
,tintWeekNo
,dteWeekEndSubmit
,'intStartUp' intColName
,intStartUp intColValue
,cc.vchrFile_number
FROM tbl_bpm_customer_collection cc
UNION ALL
SELECT
cc.vchrCustomer
,tintWeekNo
,dteWeekEndSubmit
,'intSwitcher' ColName
,intSwitcher intColValue
,cc.vchrFile_number
FROM tbl_bpm_customer_collection cc
UNION ALL
....
rest 13 columns
[/Code]
Then select from that view by "vchrCustomer" and "ColName".
_____________
Code for TallyGenerator
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply