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.
| Author |
Topic |
|
SergioM
Posting Yak Master
170 Posts |
 Posted - 2011-09-08 : 00:55:47
|
I have to write the following code six times. For US, BR, FR, CN, AU & CA. It looks identical each time, except for the 2 digit abbreviation which is referenced five times in each set. Is it possible to make this into a function (or something similar) and just pass it the 2 digit variable?-- US Items.Price_US, ROUND(Items.PriceUSFixedDifference,2) AS 'PriceUSFixedDifference', ROUND(Items.PriceUSPercentDifference,1) AS 'PriceUSPercentDifference', |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-08 : 02:30:43
|
I would create a VIEW.Our tables are named like MMM_SSS_TTT_MyTableName - where MMM=Module (EC=eCommerce), SSS=Section (SOP=Sales Order Processing), TTT=Nmemonic for table (ADR=Address), so I might have a table EX_SOP_ADR_AddressThe naming convention is not important, but I decribe it becuase using our naming convention we then have "EX_SOP_ADR_View" which is a View exposing all calculated / lookup columnsCREATE VIEW dbo.EX_SOP_ADR_ViewASSELECT [V_ex_sop_adr_ID] = ex_sop_adr_ID, [V_PriceUSFixedDifference] = ROUND(PriceUSFixedDifference,2), [V_PriceUSPercentDifference] = ROUND(PriceUSPercentDifference,1), [V_ex_sop_adr_sys_cc_Code_DESC] = CC.ec_sys_cc_CountryName, ...FROM dbo.EX_SOP_ADR_Address LEFT OUTER JOIN dbo.EX_SYS_CC_CountryCode AS CC ON ex_sys_cc_Code = ex_sop_adr_sys_cc_Code and then I can use this any of the available fields in the view something like this:SELECT Price_US, V_PriceUSFixedDifference, V_PriceUSPercentDifference, ...FROM dbo.EX_SOP_ADR_Address JOIN dbo.EX_SOP_ADR_View ON V_ex_sop_adr_ID = ex_sop_adr_ID If the logic changed for PriceUSFixedDifference etc. I can just change/fix the View |
 |
|
|
SergioM
Posting Yak Master
170 Posts |
Posted - 2011-09-08 : 15:22:16
|
Wow, thanks for the response. I do need to learn about views in SQL next. In this particular case it seems that the amount of text involved is greater than the text saved, so I'll leave it as it is. It is cleaner though. To be honest, I was expecting something more like this.selectFunction(countryVar) { Items.Price_%countryVar%, ROUND(Items.Price%countryVar%FixedDifference,2) AS 'Price%countryVar%FixedDifference', ROUND(Items.Price%countryVar%PercentDifference,1) AS 'Price%countryVar%PercentDifference', }selectFunction("US")selectFunction("BR")selectFunction("FR")selectFunction("CN")selectFunction("AU")selectFunction("CA") |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-08 : 17:12:01
|
"In this particular case it seems that the amount of text involved is greater than the text saved, so I'll leave it as it is"Depends in how many places you need it, and what the risk is that the logic will change (or you will find a bug that needs fixing).If it is trivial, and you duplicate the logic in lots of places and THEN find that there is a logic or typographical error you will wish that you had created a VIEW to centralise the bug-fix  |
|
|
|
|
|
|
|
|
Can I pass a variable to a function in SQL?