How can I execute an SQL statment inside a udf in SQL?

Question

0.00/5 (No votes)

See more:

I want to execute an string expression like (7*8/4+1) inside a function how can i achieve it inside a function.
Set @variable = (@Expr) is not helping, as @variable is considering @Expr as a string variable and not a command.
Please Note : I have append the expression as 'Select ' +@Expr.
What wrong am i doing here. Most of the things i found in the internet are of no use as i cant use EXEC inside a function as it returns only if the command is executed or not rather than the result of the command.
How can i make the @expr to behave as an sql command when it comes to the line
SET @variable = (@expr).
Please guide me through

What I have tried:

declare @evaluated decimal(18,2)
declare @vexpr varchar(MAX) = ((15 * 4) + 1)
declare @formula varchar(max) = @vexpr
SET @formula = 'Select ' +@formula
SET @formula = REPLACE(@formula , '''', '')
SET @evaluated = (@formula)

return @evaluated;

Code posted any leads or helps

When i execute the above function i get an error as below :-
Error converting data type varchar to numeric.

Posted 23-Apr-18 2:39am

Yogi@FLG

Updated 23-Apr-18 18:57pm

v4

Add a Solution

Comments

Patrice T 23-Apr-18 9:08am

Show your code.

Yogi@FLG 24-Apr-18 0:22am

declare @evaluated decimal(18,2)
declare @vexpr varchar(MAX) = ((15 * 4) + 1)
declare @formula varchar(max) = @vexpr
SET @formula = 'Select ' +@formula
SET @formula = REPLACE(@formula , '''', '')
SET @evaluated = (@formula)

return @evaluated;

Code posted any leads or helps

Patrice T 24-Apr-18 2:40am

Use Improve question to update your question.
So that everyone can pay attention to this information.

Yogi@FLG 24-Apr-18 0:33am

When i execute the above function i get an error
Error converting data type varchar to numeric.

Yogi@FLG 24-Apr-18 0:33am

When i execute the code i get the error

Error converting data type varchar to numeric.

CHill60 23-Apr-18 9:09am

Post the code rather than trying to describe it

Yogi@FLG 24-Apr-18 0:21am

declare @evaluated decimal(18,2)
declare @vexpr varchar(MAX) = ((15 * 4) + 1)
declare @formula varchar(max) = @vexpr
SET @formula = 'Select ' +@formula
SET @formula = REPLACE(@formula , '''', '')
SET @evaluated = (@formula)

return @evaluated;

Code posted any leads or helps

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santosh kumar Pithani · Answer 1 · 2018-04-23T18:58:00

Solution 1

SQL

CREATE TABLE #TEMP(formula NVARCHAR(500))
INSERT INTO #TEMP(formula ) 
    VALUES('((15 * 4) + 1)'),('((15/4) + 1)'),('((15 * 4) *(1+7))');

DECLARE @Grandtot NVarchar(500),@Query NVARCHAR(MAX)

SELECT @Grandtot=STUFF((SELECT '+CAST('+formula+' AS FLOAT)' FROM #TEMP FOR XML 
                     PATH(''),type).value('.','NVARCHAR(500)'),1,1,'');

SET @Query=N'SELECT '+@Grandtot+' AS GrandTot';

EXEC(@Query);



OUTPUT:
------
GrandTot
-----
545

Posted 23-Apr-18 18:58pm

Santosh kumar Pithani

Updated 24-Apr-18 2:32am

v2

Comments

Yogi@FLG 24-Apr-18 2:03am

But i need to call this from a function and functions do not support SPs.
Any other work around for the problem?

CHill60 24-Apr-18 4:59am

If you are trying to call this from a function, should that calling function not be a stored procedure? What are you actually trying to achieve?

Yogi@FLG 24-Apr-18 5:10am

Calling function is a scalar valued function where i have to evaluate a mathematical formula/expression based on some conditions using some parameters and the values to that formula are based on the parameters passed to that function. I get the formula only inside the function and after evaluating that expression i return that value .

Santosh kumar Pithani 24-Apr-18 5:44am

Hello Yogi, you can use scalar function BY extra parameters like ex:-((@a * @b) + @c) inside your function.

Yogi@FLG 24-Apr-18 5:58am

Hello Santhosh, that is not my exact issue i have a,b and c but how do i evaluate the expression like take your example ((@a*@b)+@c) and return that value i am replacing that a,b and c and now i want to get the value for my mathematical expression.

To be more fluent i am getting that expression ((@a*@b)+@c)within the function itself , i have a formula which i get from the parameters passed to that function , i cannot pass even the formula as for the particular conditions i may have more than one formula or expression and after i get the expression evaluated i will sum it and return that sum value.

Santosh kumar Pithani 24-Apr-18 6:18am

If you knew all mathematical formulas then use them directly inside function by if condition.

Yogi@FLG 24-Apr-18 6:29am

If i knew all the formulas then why would i even write a function in the first place and write my query here.
it is a series of formulas i get i would get based on some random conditions while executing my function.
I want to execute that expression fro those series of formulas.

Santosh kumar Pithani 24-Apr-18 6:47am

How you get all formulas,have any table for storing all formulas ?

Yogi@FLG 24-Apr-18 6:54am

Yes. I have table where all formulas are stored.

Santosh kumar Pithani 24-Apr-18 7:06am

Ok, Use XML Stuff function to make Grandtotal and then append to dynamic query

Yogi@FLG 24-Apr-18 7:15am

Ok let me try...

Yogi@FLG 24-Apr-18 7:35am

I would get only expressions but how can i get the evaluated value of those expressions/formulas. Or i am wrong in understanding the concept could you help me with a sample example for my requirement.

Santosh kumar Pithani 24-Apr-18 8:11am

Sure

Santosh kumar Pithani 24-Apr-18 8:34am

Hello Yogi, solution is updated check it once...if okay mark it as solution.