April 5, 2018 at 8:19 pm
Hello,
I'm working on a report for a customer where they have a formula stored in a varchar field such as "({Price} - 8.1) x .7 x {Weight}". I'm using the Replace function to replace {Price} with the actual price value for the record and same with Weight, but then I run into how to do the actual calculation? So what I end up having after the Replace function is something like "(3.58 - 8.1) x .7 x 6.457" in another varchar field. Does anyone have any suggestions on the best way to go about getting the end result of the calculation (-20.429948)?
TIA!
Leisa
April 6, 2018 at 3:58 am
lk4772 - Thursday, April 5, 2018 8:18 PMHello,
I'm working on a report for a customer where they have a formula stored in a varchar field such as "({Price} - 8.1) x .7 x {Weight}". I'm using the Replace function to replace {Price} with the actual price value for the record and same with Weight, but then I run into how to do the actual calculation? So what I end up having after the Replace function is something like "(3.58 - 8.1) x .7 x 6.457" in another varchar field. Does anyone have any suggestions on the best way to go about getting the end result of the calculation (-20.429948)?TIA!
Leisa
You need to replace you "x" with "*" as the multiplication sign.
You need to replace your {FieldName} with @ParameterName
Then this will work
DECLARE @Formula NVARCHAR(100);
DECLARE @Result DECIMAL(18,6);
SET @Formula = N'SELECT @Result=' + REPLACE(REPLACE(N'({Price} - 8.1) * .7 * {Weight}', N'{Price}', N'@Price'), N'{Weight}', N'@Weight');
EXEC sys.sp_executesql
@stmt = @Formula
, @params = N'@Price DECIMAL(18,6), @Weight DECIMAL(18,6), @Result DECIMAL(18,6) OUTPUT'
, @Price = 3.58
, @Weight = 6.457
, @Result = @Result OUTPUT;
SELECT Formula = @Formula, Result = @Result;
April 6, 2018 at 9:50 am
Thank you so much for the quick response DesNorton! However, I should have mentioned, in the environment I'm working in, I'm not able to use SP_ExecuteSQL. Do you know of any other way to do this?
April 6, 2018 at 12:27 pm
lk4772 - Friday, April 6, 2018 9:50 AMThank you so much for the quick response DesNorton! However, I should have mentioned, in the environment I'm working in, I'm not able to use SP_ExecuteSQL. Do you know of any other way to do this?
But they'd let a developer write any other bad code they wanted, I'll bet. There's really no good reason for such a thing. You can't do anything with sp_ExecuteSQL that you couldn't do other ways, such as use a stored procedure, and at least the sp_ExecuteSQL helps avoid SQL injection from dynamic SQL.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 6, 2018 at 12:33 pm
Preaching to the choir sgmunson! LOL Just wondering if there's a better way than parsing it all out....
April 6, 2018 at 1:07 pm
lk4772 - Friday, April 6, 2018 12:33 PMPreaching to the choir sgmunson! LOL Just wondering if there's a better way than parsing it all out....
Ask why that restriction is in place... It's not doing anyone any good...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 6, 2018 at 1:46 pm
lk4772 - Friday, April 6, 2018 9:50 AMThank you so much for the quick response DesNorton! However, I should have mentioned, in the environment I'm working in, I'm not able to use SP_ExecuteSQL. Do you know of any other way to do this?
It's the only way that I can think of in SQL.
Otherwise pass the formula and the values back to the application, and let them do it ther.
April 9, 2018 at 2:56 pm
Can you use EXEC? In other words, is all dynamic SQL off-limits or is the prohibition only against sp_ExecuteSQL?
(Yes, yes, I know: That would make no sense.)
You can run dynamic SQL using EXEC, it's just more of a security hazard, requiring more scrubbing of your inputs
If that calculation is stored in a table, how are they CURRENTLY using it with SQL?
*Why* is the calculation code stored in a table? Is there logic that lets you choose between different calculations to get a result?
If so, that could be accomplished with a CASE expression or a VALUES clause.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 9, 2018 at 3:47 pm
I suppose you could create a computed column(s) in a dummy table, with a base dummy column, assuming you could live with the overhead of creating a separate computed column for every expression. You could drop each computed column and row after you used it.
If you'd like more details on this approach, just let me know.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 11, 2018 at 2:55 pm
Hello,
Thanks for the responses everyone! So I got the go ahead from the parent company to create a utility function to use the sp_executesql however when I test it I get the message "Msg 557, Level 16, State 2, Line 1
Only functions and some extended stored procedures can be executed from within a function." Is there a way to be able to use sp_executesql in a function?
April 11, 2018 at 10:14 pm
There are some work-arounds, but best not to try. Rewrite it as a stored procedure, and return the result as either the return value if integer, or as an output parameter.
April 13, 2018 at 9:50 am
lk4772 - Wednesday, April 11, 2018 2:55 PMHello,Thanks for the responses everyone! So I got the go ahead from the parent company to create a utility function to use the sp_executesql however when I test it I get the message "Msg 557, Level 16, State 2, Line 1
Only functions and some extended stored procedures can be executed from within a function." Is there a way to be able to use sp_executesql in a function?
Nope. You can never EXEC anything within a function. Only stored procedures can do that.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 13, 2018 at 9:59 am
Will this work?
exec ('select ' + @Formula);
April 13, 2018 at 11:58 am
Bert-701015 - Friday, April 13, 2018 9:59 AMWill this work?
exec ('select ' + @Formula);
Not within a function. You can't use dynamic SQL in a function either.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply