Hey guys, I have a non-CSLA question. This sproc is used by a CSLA object, the the question in nature is not CSLA centered.
I have many products, each product can have many prices (MSRP, Street, Jobber, etc.). The users of the website are authorized to see products at a specific calculation off of a specific price.
So User_A may be able to see MSRP + 10 and User_B may be able to see Jobber + Jobber * .10. These calculations are stored in a table.
So I have a pretty complex query and where I'm stuck is this, basically.
How do I say
SELECT Product_ID, (PriceType) as Quoted Price
FROM tblRLProducts p JOIN tbRLProduct_PriceAuthorizations pa ON p.Brand_ID = pa.Brand_ID
WHERE PriceType is a Field Name I'm pulling from the Price AUthorizations table. The actual query is much more complicated. Where (PriceType) is there is a CASE statement that accounts for all the different kinds of adjustments and there are a ton of WHERE clause statements and security tables included.
But basically I want to select a field from one table but I am pulling the name of that field from another linked table and it can be different for each product so it's not like I could select the Price Type then inject it into the SQL.
I don't think it's normalization because every product needs every type of price. One customer may see MSRP, another will see Jobber, another will see Street. And some users get to see all prices. So every product needs every type of price.
The problem is when they're in the search form they select the price they want to see. Some people will select Jobber and have access straight to jobber and I can use dynamic sql to pass in the pricetype parameter. However, for others the only choice is AuthorizedPrice adn the user doens't know if it's a calculation off msrp, jobber, user, street, or what, only the Authorizor knows that. So they get a calculated price that pulls from one of the defined price types. But that authorization is different for different users and different for different products.
But at one point or another every products needs every price.
I understand what you're saying and I wouldn't say your wrong and I'm right, but it think it's a debatable issue. Our price types haven't changed since we built the original system 6 years ago and they're industry specific prices that are pretty standard. But I do understand what you're saying.
However, our system is way too far along to make that kind of change, parts are already in production and we can't at this point change that.
So knowing that, is there a way to take a Field Name from one table in the JOIN and use it to select that fields value from another table in the join. That's the question I'm really looking to answer.
Itgrady:
However, our system is way too far along to make that kind of change, parts are already in production and we can't at this point change that.
So knowing that, is there a way to take a Field Name from one table in the JOIN and use it to select that fields value from another table in the join. That's the question I'm really looking to answer.
Don't have time to study your model to see whether it could be better normalized.
(You don't have time to change it anyway, so we're even! :)
Take your complicated logic and put it into a DATABASE function that returns the price.
Your statement would then look like:
select blah-blah, getPrice(productid, customerid, etc.) as the_price_to_use
from somewhere.
The function can be as complicated as you want inside (and very procedural in nature, which sounds like what you need), but it will be very easy to use.
I've never used a database function. So I can pass in value from fields in my select and it returns me a value? That sounds like it could be a winner.
So it would be like
SELECT p.Product_ID, p.Product_Name, getPrice(a.PriceType,@userID) as AuthorizedPrice
FROM tblProducts p JOIN tblProducts_AuthorziedPricing a ON p.Product_ID=a.Product_ID
Going to look at that right now, how is the cost?
ajj3085:
Yes, the function is probably a better idea than the proc method, as it gives you the flexibility to include the function output in a view. ...
Although you may want to consider doing the 'sorting out' in the business layer as well, since which price to return is actually a business rule. Returning a few extra columns shouldn't matter much in the grand scheme of things, and there'd be minimal peformance impact doing that.
Valid points, if it's just "a few extra columns in the same query". But I get the impression that is not the case, that lots of different tables and different columns need to be checked.
The problem with with db function is that it can hurt peformance quite a bit; I had an issue once were we did do a very similar thing, and unfortunately there wasn't really any good way of speeding up the function. We reworked the data model to get things 'right'.
Here I beg to differ! As you described it, the issue was not with the db function at all. It was with the data model! Had your BO layer issued the same queries to get the data as the db function did (and it would have had to, wouldn't it!), you would have gotten even slower results! (You would also have had to deal with network latency.) In that case, after the data model was fixed the db function might have been much faster also.
Some other advantages of doing it in the BO are that you're Db-independant, so if you need to port to a db that doesn't support functions, you're fine,
Agreed.
and also the C# code will be much easier to maintain than the Sql function.
Speak for yourself! :) Well, actually, yourself and the original poster. :)
Which is easier to maintain depends upon relative fluency in the two languages and the nature of the algorithm needed. I've seen hundreds of lines of C# code replaced by a just a few lines of very maintainable SQL. Not being as proficient in C# as I am in SQL, I haven't seen the opposite as often, but I suspect that's just a shortcoming on my part.
Ok, I think I almost have this working but I can't get my value out of the function. I have the select string I want to execute but I can't ge the EXEC to load into my variable to return it.
Here it is, when I try to save I get an error, unspecified, with the exec command. I've tried a couple variations and can't seem to work it out. I keep getting an error on the EXEC line.
Here it is:
SET
ANSI_NULLS ONSET
QUOTED_IDENTIFIER ONALTER
FUNCTION rlGetPriceRETURNS
moneyAS
BEGIN
DECLARE @QuotedPrice money,@SQL varchar(500),@SQL_Price varchar(100)IF
@AdjType IS NULLELSE
![Wink [;)]](/emoticons/emotion-5.gif)
I got the select string and it's working. With both sets of parameters I get the right value, calculated or straight.
But my problem is I can't get that value out of the function.
I have the SElect string, I can return a money, but I how do I get the select string to run, get my number, and assign it to the variable.
Set @QuotedPrice = EXEC(@SQL)
Return @QuotedPrice
That's not working.
Well unlike my attempt at it, at least it let me save the function this time.
However when I run it, using your syntax, it says.
'Remote function calls are not allowed within a function.'
Any other ideas?
Professional SQL Server 2000 Programming, by Robert Vieira, says that Exec cannot be used within a user defined function.
Conceptually, that's because functions can be used by select statements and exec statements could cause updates to data. (It's also because the SQL Server team didn't provide a "exec_select" routine that would guarantee a select was being issued, but I digress...)
Try something like this. It has the advantage of not using dynamic sql, which is therefore faster and less prone to sql injection. Also much easier to maintain! Don't have your tables to compile and test against, so there might be some typos.
Obviously, you will have to supply the correct possible column names for the first select statement in the function I supplied. New columns will require a re-write of that portion of code (unlike your original approach). Sounds like that portion of the model is pretty stable.
That said, if your function just returned the value for @BaseQuotedPrice, the rest of the algorithm could easily be in the C# code if that makes you happier on a philosophical basis. :)
Hope this helps!
SET
ANSI_NULLS ONSET
QUOTED_IDENTIFIER ONALTER
FUNCTION rlGetPrice DECLARE @QuotedPrice money
DECLARE @BaseQuotedPrice money
SELECT @BaseQuotedPrice = CASE @PriceType
WHEN 'pricetype_a' THEN a
WHEN 'pricetype_b' THEN b
WHEN 'pricetype_c' THEN c
WHEN 'pricetype_d' THEN d
END as QuotedPrice
FROM tblRL_Products JOIN tblRL_ProductMkt_Specials
ON tblRL_Products.Product_ID = tblRL_ProductMkt_Special.Product_ID
WHERE tblRL_Products.Product_ID = @Product_ID
IF
@AdjType IS NULLRETURN (@QuotedPrice)
END
Valid points, if it's just "a few extra columns in the same query". But I get the impression that is not the case, that lots of different tables and different columns need to be checked.
![Smile [:)]](/emoticons/emotion-1.gif)
Here I beg to differ! As you described it, the issue was not with the db function at all. It was with the data model!
However, the underlying issue here is the datamodel, which we've already said was less than ideal. So my point that a function may not be the solution stands, if the function solution gives poor peformance.
Had your BO layer issued the same queries to get the data as the db function did (and it would have had to, wouldn't it!), you would have gotten even slower results! (You would also have had to deal with network latency.)
Not necessarly; if you have a way to batch all the sql commands into one Execute method, you can reduce the network latency by a pretty large factor. By doing some smart batching we were able to take a process that took 1.5 hours down to one that took 25 minutes.
In that case, after the data model was fixed the db function might have been much faster also.
Fixing the data model in my case removed the need for such a function at all; its likely this is true here as well.
Speak for yourself! :) Well, actually, yourself and the original poster. :)
Yes this is true, although my experience has lead me to the conclusion that script languages are almost always harder to maintain than a strongly typed, compiled language. The script languages I've used most of my career are T-Sql, VBS (ASP), JavaScript and "sh script". Perhaps its the horrid memory of ASP which makes me loath script so much...Which is easier to maintain depends upon relative fluency in the two languages and the nature of the algorithm needed. I've seen hundreds of lines of C# code replaced by a just a few lines of very maintainable SQL. Not being as proficient in C# as I am in SQL, I haven't seen the opposite as often, but I suspect that's just a shortcoming on my part.
![Big Smile [:D]](/emoticons/emotion-2.gif)
I've never used a database function. So I can pass in value from fields in my select and it returns me a value? That sounds like it could be a winner.
So it would be like
SELECT p.Product_ID, p.Product_Name, getPrice(a.PriceType,@userID) as AuthorizedPrice
FROM tblProducts p JOIN tblProducts_AuthorziedPricing a ON p.Product_ID=a.Product_ID
Yep. That's exactly right
Going to look at that right now, how is the cost?
That depends!
If the queries that acquire the information you need take a long time to run, it will be slow. Otherwise it will be pretty fast. Not quite as fast as if you could bring back all the fields you need in the main query and do the math inline in the select clause, but still pretty fast. It just depends upon how many queries you have to issue for any given calculation and how long they take to run.
I was worried about speed bringing it all into the biz object and manipulating it there.
I was also considering loading the values all into a #temptable and then calculating in that in a couple steps and then pulling the results. But again, was worried about speed.
I suppose there isn't going to be a way to do this without it being a little pokey.
Copyright (c) Marimer LLC