parameterize a stored procedure by a simple mathematical expression

Question

I have a bunch of simple expressions, such as:

c=a+b
c=a*b
...

I would like to pass them as parameter to a stored procedure, which is going to perform an update using them.

CREATE TABLE t(
    a int,
    b int,
    c int
);

INSERT INTO t VALUES (1,2,3),(4,5,6);

CREATE PROCEDURE sp @left_member varchar(50), @right_member
AS 
BEGIN
    UPDATE t
    SET @left_member = @right_member
END

EXEC sp  'c', 'a+b'
EXEC sp  'c', 'a*b'

Is there a way of doing something like that ? I would like to possibly avoid dynamic SQL. In my target design, the expressions will be stored in their own table (editable online).

What you describe sounds like the very definition of dynamic SQL... — Peter B, Feb 26 '20 at 16:54
As you know by now, SQL Server does not have an Eval() function. That said, take a peek at https://stackoverflow.com/questions/51247851/execute-column-values-in-sql-server/51248368#51248368 — John Cappelletti, Feb 26 '20 at 16:57
If you don't want to write Dynamic SQL, then you could use CASE statements, but you need to know all the cases. — Ignacio Gonzalez, Feb 26 '20 at 16:57
Can you pass a function as parameter to a stored procedure ? — Ludovic Aubert, Feb 26 '20 at 17:08

score 1 · Accepted Answer · answered Feb 26 '20 at 17:03

1

I generally don't recommend doing this, but dynamic SQL is pretty much the solution:

CREATE PROCEDURE usp_exec_dangerous_update (
    @left_member nvarchar(50),
    @right_member nvarchar(50)
)
AS 
BEGIN
    DECLARE @sql NVARCHAR(MAX);

    SET @sql = N'
UPDATE t
    SET [left_member] = [right_member]
';

    SET @sql = REPLACE(REPLACE(@sql, '[left_member]', @left_member), '[right_member]', @right_member);

    EXEC sp_executesql @sql;
END;

Although such code can be useful in a thoughtful, well-designed system, in general it is not needed:

It exposes the system to SQL injection attacks. Running "generic" code is just dangerous.
It does not handle errors, which are easy to occur with this method.

answered Feb 26 '20 at 17:03

Gordon Linoff

1,242,037
58
646
786

Would it be recommended to do it in #NET then ? Using Eval() function ? – Ludovic Aubert Feb 27 '20 at 09:13
Or would it be recommended to call a python script from SQL to perform this instead ? – Ludovic Aubert Feb 27 '20 at 09:37
SQL Server 2017 using sp_execute_external_script (SQL Server Machine Learning) to run a python script in the context of the database ? – Ludovic Aubert Feb 27 '20 at 12:51
1

@LudovicAubert . . . I don't really recommend doing this type of manipulation unless you really know what you are doing. There are some circumstances where it can be important, but this is not some that is needed for "typical" applications. – Gordon Linoff Feb 27 '20 at 13:35
The security issue is language independant ? – Ludovic Aubert Feb 27 '20 at 13:59
1

@LudovicAubert . . Yes. It is a question of passing user-input as a SQL string. Validating such input is quite tricky. I have used SQL generation for some applications. In such cases, the code is internal to the application -- although a malicious user could still update values in tables that generate the code. – Gordon Linoff Feb 27 '20 at 14:00

parameterize a stored procedure by a simple mathematical expression

1 Answers1