CODEWITHSUNDEEP
sqlsql-serverstored-proceduresuser-defined-functionsdynamicquery
Hosse Fernando
Hosse Fernando

Reputation: 11

Calculate String on SQL Server Function

I'm trying to find a workaround to calculate string in SQL Server just like the eval() function on JavaScript. What makes it difficult is I need it to do it in function so that I can use it on my stored procedure's query.

Supply = '2 + 2 * 3'
Demand = '2'

SELECT Name, CAST(dbo.CalculateString(Supply) * Demand AS DECIMAL(18, 1))  AS CalculatedValue FROM Stocks;

the expected output is 
Candy 16

I understand that:

  1. using dynamic query, but I can't since function in SQL Server does not support execution of dynamic query.
  2. using stored procedure, but I need to combine the value output of the calculated string to calculate with another field too so I can't (?).

Is there any workaround for this? I'm using SQL Server 16.0.1000.6

Upvotes: 0

Views: 142

Answers (1)

Alex
Alex

Reputation: 5157

Can it be done? Yes

Do you want to do it? No

Let me demonstrate.

First let's create some sample data:

CREATE TABLE Stocks( ID INT IDENTITY, Supply VARCHAR( 50 ), Demand VARCHAR( 50 ))
INSERT INTO Stocks( Supply, Demand )
SELECT '2 + 2 * 3', '2'
UNION ALL
SELECT '2 - 2 * 3', '2 / 2'

Now, we create the procedure (Note that procedure is named appropriately) that will dynamically evaluate your columns:

CREATE OR ALTER PROCEDURE DirtyHack
AS
    SET NOCOUNT ON;

    DECLARE @ID INT, @Supply VARCHAR( 50 ), @Demand VARCHAR( 50 )
    DECLARE @EvalQuery NVARCHAR( 500 ), @EvaluatedSupply INT, @EvaluatedDemand INT

    CREATE TABLE #Result( ID INT, EvaluatedSupply INT, EvaluatedDemand INT )

    DECLARE iterator CURSOR LOCAL FAST_FORWARD
    FOR SELECT ID, Supply, Demand FROM Stocks

    OPEN iterator
    FETCH NEXT FROM iterator INTO @ID, @Supply, @Demand
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @EvalQuery = N'SET @EvaluatedSupply = ' + @Supply + '; SET @EvaluatedDemand = ' + @Demand + ';'
        EXEC sp_executesql @EvalQuery, N'@EvaluatedSupply INT OUTPUT, @EvaluatedDemand INT OUTPUT',
            @EvaluatedSupply = @EvaluatedSupply OUTPUT, @EvaluatedDemand = @EvaluatedDemand OUTPUT
        
        INSERT INTO #Result
        SELECT @ID, @EvaluatedSupply, @EvaluatedDemand

        FETCH NEXT FROM iterator INTO @ID, @Supply, @Demand
    END
    CLOSE iterator
    DEALLOCATE iterator

    SELECT ID, EvaluatedSupply, EvaluatedDemand FROM #Result

    RETURN;

The procedure is quite straightforward: using a cursor it loops through each row and dynamically evaluates/executes data your columns, then stores results to temp table. For larger table performance will be glacial.

Then we use the OPENQUERY trick to turn this stored procedure into a view:

CREATE OR ALTER VIEW VeryDirtyHack
AS
    SELECT *
    FROM OPENQUERY( SELF, 'EXEC Playground.dbo.DirtyHack WITH RESULT SETS(( ID INT, EvaluatedSupply INT, EvaluatedDemand INT ))' ) AS Eval;

SELF is the name of the linked server pointing to itself. WITH RESULT SETS is necessary to avoid SQL Server being confused about the format of the result set.

Instead of view you can create a function but it will not make any difference to the functionality.

Now you can do this:

SELECT *, EvaluatedSupply * EvaluatedDemand AS CalculatedValue
FROM Stocks AS S
    INNER JOIN VeryDirtyHack AS VDH ON S.ID = VDH.ID

Result:

ID          Supply     Demand  ID  EvaluatedSupply EvaluatedDemand CalculatedValue
----------- ---------- ------- --- --------------- --------------- ---------------
1           2 + 2 * 3  2       1   8               2               16
2           2 - 2 * 3  2 / 2   2   -4              1               -4

This is about as "hacky" as you can go in SQL Server. Performance will suffer due to RBAR execution in the procedure. As there is no way to pass parameters to OPENQUERY function, there is no way to "optimise" the stored procedure.

Last but not least: if you directly execute your users' input (as dynamic SQL) on the server then this opens big security holes in your system, as instead of 2 + 2 users can enter ; DROP DATABASE xyz; just for fun.

Upvotes: 1

Related Questions