Using Dynamic Query in a Transact SQL Select

Question

Using Dynamic Query in a Transact SQL Select

imanalimi

SSC Journeyman

Points: 87
More actions
August 22, 2017 at 8:49 pm

#332303

Hi Guys,
I am trying to calculate formula using SQL Server 2016.
How can I get the result of a Formula within a Select Statement like this:
CREATE TABLE #tblFormula (C1 INT, C2 INT,Formula NVARCHAR(50)) INSERT INTO #tblFormula (C1,C2,Formula) VALUES (5,1,'C1+C2') ,(7,2.5,'C1*C2') ,(4.5,8,'C1/C2');
SELECT C1,C2,Formula,'Result' AS [Result] FROM #tblFormula
DROP TABLE #tblFormula;
The Result would be similar to:
C1....C2....Formula....Result
5........1.....C1+C2........6
7........2.....C1*C2........14
4........8.....C1/C2........0.5
Many Thanks

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

imanalimi - Tuesday, August 22, 2017 8:49 PM
Hi Guys,
I am trying to calculate formula using SQL Server 2016.
How can I get the result of a Formula within a Select Statement like this:
CREATE TABLE #tblFormula (C1 INT, C2 INT,Formula NVARCHAR(50)) INSERT INTO #tblFormula (C1,C2,Formula) VALUES (5,1,'C1+C2') ,(7,2.5,'C1*C2') ,(4.5,8,'C1/C2'); SELECT C1,C2,Formula,'Result' AS [Result] FROM #tblFormula
DROP TABLE #tblFormula;
The Result would be similar to:
C1....C2....Formula....Result
5........1.....C1+C2........6
7........2.....C1*C2........14
4........8.....C1/C2........0.5
Many Thanks

I do not see a way to do this within one statement however you may be able to achieve it with some procedural T-SQL. It would simple to do this in an app code language, perhaps in C# inside a SQLCLR table-valued function.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

I would recommend a procedural language and not tsql for a problem such as this. Using tsql to do this would be like trying to use a sledgehammer to screw on a bolt - it's just the wrong tool.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

SQLRNNR - Tuesday, August 22, 2017 10:38 PM
I would recommend a procedural language and not tsql for a problem such as this. Using tsql to do this would be like trying to use a sledgehammer to screw on a bolt - it's just the wrong tool.

+1

Pedantic comment on it's way...technically T-SQL is a procedural language but we're aligned on the main point to look beyond T-SQL. I started on a PoC to solve this with T-SQL and stopped when combining a cursor with some dynamic SQL started presenting itself as a potential solution.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

sgmunson SSC Guru Points: 110640 More actions · Answer 4

A few things about this:

1.) This would be very difficult to achieve in T-SQL
2.) Why would you want to do this? There are much better tools already available (e.g. Mathematica)
3.) T-SQL is a set-based language, NOT a procedural one. Just because you can use it that way doesn't make it procedural.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 5

This is likely not the best way to do it, but I think this should work the way you want (with the exception that your result set is wrong based on the provided data):
CREATE TABLE [#tblFormula]
    (
        [C1] FLOAT ,
        [C2] FLOAT ,
        [Formula] NVARCHAR(50)
    );
CREATE TABLE [#tblSolution]
    (
        [C1] FLOAT ,
        [C2] FLOAT ,
        [Formula] NVARCHAR(50) ,
        [result] VARCHAR(50)
    );
INSERT INTO [#tblFormula] (
                             [C1] ,
                             [C2] ,
                             [Formula]
                         )
VALUES
    (
        5, 1, 'C1+C2'
    ) ,
    (
        7, 2.5, 'C1*C2'
    ) ,
    (
        4.5, 8, 'C1/C2'
    );

DECLARE
    @query VARCHAR(MAX) ,
    @c1 VARCHAR(3) ,
    @c2 VARCHAR(3) ,
    @formula VARCHAR(255);
DECLARE [cursewords] CURSOR LOCAL FAST_FORWARD FOR
    SELECT
        [#tblFormula].[C1] ,
        [#tblFormula].[C2] ,
        [#tblFormula].[Formula]
    FROM
        [#tblFormula];
OPEN [cursewords];
FETCH NEXT FROM [cursewords]
INTO
    @c1 ,
    @c2 ,
    @formula;
WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT
            @query = 'SELECT ' + @c1 + ', ' + @c2 + ', Formula, CAST('
                     + @formula
                     + ' AS VARCHAR(50)) FROM #tblFormula WHERE C1 = ' + @c1
                     + ' AND C2 = ' + @c2 +' AND Formula = ''' + @formula + '''';
        --SELECT @query
        INSERT INTO [#tblSolution] (
                                     [C1] ,
                                     [C2] ,
                                     [Formula] ,
                                     [result]
                                 )
        EXEC ( @query );
        FETCH NEXT FROM [cursewords]
        INTO
            @c1 ,
            @c2 ,
            @formula;
    END;
SELECT
    [#tblSolution].[C1] ,
    [#tblSolution].[C2] ,
    [#tblSolution].[Formula] ,
    [#tblSolution].[result]
FROM
    [#tblSolution];
DROP TABLE [#tblFormula];
DROP TABLE [#tblSolution];
The results are:
C1....C2....Formula....Result
5........1.....C1+C2........6
7........2.5..C1*C2........17.5
4.5.....8.....C1/C2........0.5625

Does that meet your requirements?
While I do agree that SQL isn't the best tool for this job, it can get the job done!

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

bmg002 - Wednesday, August 23, 2017 10:40 AM
This is likely not the best way to do it, but I think this should work the way you want (with the exception that your result set is wrong based on the provided data):
CREATE TABLE [#tblFormula]
    (
        [C1] FLOAT ,
        [C2] FLOAT ,
        [Formula] NVARCHAR(50)
    );
CREATE TABLE [#tblSolution]
    (
        [C1] FLOAT ,
        [C2] FLOAT ,
        [Formula] NVARCHAR(50) ,
        [result] VARCHAR(50)
    );
INSERT INTO [#tblFormula] (
                             [C1] ,
                             [C2] ,
                             [Formula]
                         )
VALUES
    (
        5, 1, 'C1+C2'
    ) ,
    (
        7, 2.5, 'C1*C2'
    ) ,
    (
        4.5, 8, 'C1/C2'
    );
DECLARE
    @query VARCHAR(MAX) ,
    @c1 VARCHAR(3) ,
    @c2 VARCHAR(3) ,
    @formula VARCHAR(255);
DECLARE [cursewords] CURSOR LOCAL FAST_FORWARD FOR
    SELECT
        [#tblFormula].[C1] ,
        [#tblFormula].[C2] ,
        [#tblFormula].[Formula]
    FROM
        [#tblFormula];
OPEN [cursewords];
FETCH NEXT FROM [cursewords]
INTO
    @c1 ,
    @c2 ,
    @formula;
WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT
            @query = 'SELECT ' + @c1 + ', ' + @c2 + ', Formula, CAST('
                     + @formula
                     + ' AS VARCHAR(50)) FROM #tblFormula WHERE C1 = ' + @c1
                     + ' AND C2 = ' + @c2 +' AND Formula = ''' + @formula + '''';
        --SELECT @query
        INSERT INTO [#tblSolution] (
                                     [C1] ,
                                     [C2] ,
                                     [Formula] ,
                                     [result]
                                 )
        EXEC ( @query );
        FETCH NEXT FROM [cursewords]
        INTO
            @c1 ,
            @c2 ,
            @formula;
    END;
SELECT
    [#tblSolution].[C1] ,
    [#tblSolution].[C2] ,
    [#tblSolution].[Formula] ,
    [#tblSolution].[result]
FROM
    [#tblSolution];
DROP TABLE [#tblFormula];
DROP TABLE [#tblSolution];
The results are:
C1....C2....Formula....Result
5........1.....C1+C2........6
7........2.5..C1*C2........17.5
4.5.....8.....C1/C2........0.5625
Does that meet your requirements?
While I do agree that SQL isn't the best tool for this job, it can get the job done!

Interesting solution.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 7

SQLRNNR - Wednesday, August 23, 2017 10:53 AM
bmg002 - Wednesday, August 23, 2017 10:40 AM
This is likely not the best way to do it, but I think this should work the way you want (with the exception that your result set is wrong based on the provided data):
CREATE TABLE [#tblFormula]
    (
        [C1] FLOAT ,
        [C2] FLOAT ,
        [Formula] NVARCHAR(50)
    );
CREATE TABLE [#tblSolution]
    (
        [C1] FLOAT ,
        [C2] FLOAT ,
        [Formula] NVARCHAR(50) ,
        [result] VARCHAR(50)
    );
INSERT INTO [#tblFormula] (
                             [C1] ,
                             [C2] ,
                             [Formula]
                         )
VALUES
    (
        5, 1, 'C1+C2'
    ) ,
    (
        7, 2.5, 'C1*C2'
    ) ,
    (
        4.5, 8, 'C1/C2'
    );
DECLARE
    @query VARCHAR(MAX) ,
    @c1 VARCHAR(3) ,
    @c2 VARCHAR(3) ,
    @formula VARCHAR(255);
DECLARE [cursewords] CURSOR LOCAL FAST_FORWARD FOR
    SELECT
        [#tblFormula].[C1] ,
        [#tblFormula].[C2] ,
        [#tblFormula].[Formula]
    FROM
        [#tblFormula];
OPEN [cursewords];
FETCH NEXT FROM [cursewords]
INTO
    @c1 ,
    @c2 ,
    @formula;
WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT
            @query = 'SELECT ' + @c1 + ', ' + @c2 + ', Formula, CAST('
                     + @formula
                     + ' AS VARCHAR(50)) FROM #tblFormula WHERE C1 = ' + @c1
                     + ' AND C2 = ' + @c2 +' AND Formula = ''' + @formula + '''';
        --SELECT @query
        INSERT INTO [#tblSolution] (
                                     [C1] ,
                                     [C2] ,
                                     [Formula] ,
                                     [result]
                                 )
        EXEC ( @query );
        FETCH NEXT FROM [cursewords]
        INTO
            @c1 ,
            @c2 ,
            @formula;
    END;
SELECT
    [#tblSolution].[C1] ,
    [#tblSolution].[C2] ,
    [#tblSolution].[Formula] ,
    [#tblSolution].[result]
FROM
    [#tblSolution];
DROP TABLE [#tblFormula];
DROP TABLE [#tblSolution];
The results are:
C1....C2....Formula....Result
5........1.....C1+C2........6
7........2.5..C1*C2........17.5
4.5.....8.....C1/C2........0.5625
Does that meet your requirements?
While I do agree that SQL isn't the best tool for this job, it can get the job done!
Interesting solution.

Thanks. At least I think that is the appropriate response. I am hoping you mean "oh, cool. That is an interesting solution to the problem" and not "wow... that is so many flavors of bad it's interesting that anyone in their right mind would even attempt to do it that way".
I'm sure there is a better (more efficient and cleaner) way to do this, I just went with the first solution that came to mind. I don't really like using cursors where they are not needed, but I think that is the only way to get the solution with this problem. Thinking about the solution a bit more, I think the last SELECT should likely be a SELECT DISTINCT as there could be cases where you have duplicate rows in #tblFormula and you likely don't need duplicate results. If the rows had some uniquifier (an identity for example), then the WHERE clause on the dynamic SQL could be cleaner and it would handle duplicate rows better.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

bmg002 - Wednesday, August 23, 2017 11:04 AM
SQLRNNR - Wednesday, August 23, 2017 10:53 AM
bmg002 - Wednesday, August 23, 2017 10:40 AM
This is likely not the best way to do it, but I think this should work the way you want (with the exception that your result set is wrong based on the provided data):
CREATE TABLE [#tblFormula]
    (
        [C1] FLOAT ,
        [C2] FLOAT ,
        [Formula] NVARCHAR(50)
    );
CREATE TABLE [#tblSolution]
    (
        [C1] FLOAT ,
        [C2] FLOAT ,
        [Formula] NVARCHAR(50) ,
        [result] VARCHAR(50)
    );
INSERT INTO [#tblFormula] (
                             [C1] ,
                             [C2] ,
                             [Formula]
                         )
VALUES
    (
        5, 1, 'C1+C2'
    ) ,
    (
        7, 2.5, 'C1*C2'
    ) ,
    (
        4.5, 8, 'C1/C2'
    );
DECLARE
    @query VARCHAR(MAX) ,
    @c1 VARCHAR(3) ,
    @c2 VARCHAR(3) ,
    @formula VARCHAR(255);
DECLARE [cursewords] CURSOR LOCAL FAST_FORWARD FOR
    SELECT
        [#tblFormula].[C1] ,
        [#tblFormula].[C2] ,
        [#tblFormula].[Formula]
    FROM
        [#tblFormula];
OPEN [cursewords];
FETCH NEXT FROM [cursewords]
INTO
    @c1 ,
    @c2 ,
    @formula;
WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT
            @query = 'SELECT ' + @c1 + ', ' + @c2 + ', Formula, CAST('
                     + @formula
                     + ' AS VARCHAR(50)) FROM #tblFormula WHERE C1 = ' + @c1
                     + ' AND C2 = ' + @c2 +' AND Formula = ''' + @formula + '''';
        --SELECT @query
        INSERT INTO [#tblSolution] (
                                     [C1] ,
                                     [C2] ,
                                     [Formula] ,
                                     [result]
                                 )
        EXEC ( @query );
        FETCH NEXT FROM [cursewords]
        INTO
            @c1 ,
            @c2 ,
            @formula;
    END;
SELECT
    [#tblSolution].[C1] ,
    [#tblSolution].[C2] ,
    [#tblSolution].[Formula] ,
    [#tblSolution].[result]
FROM
    [#tblSolution];
DROP TABLE [#tblFormula];
DROP TABLE [#tblSolution];
The results are:
C1....C2....Formula....Result
5........1.....C1+C2........6
7........2.5..C1*C2........17.5
4.5.....8.....C1/C2........0.5625
Does that meet your requirements?
While I do agree that SQL isn't the best tool for this job, it can get the job done!
Interesting solution.
Thanks. At least I think that is the appropriate response. I am hoping you mean "oh, cool. That is an interesting solution to the problem" and not "wow... that is so many flavors of bad it's interesting that anyone in their right mind would even attempt to do it that way".
I'm sure there is a better (more efficient and cleaner) way to do this, I just went with the first solution that came to mind. I don't really like using cursors where they are not needed, but I think that is the only way to get the solution with this problem. Thinking about the solution a bit more, I think the last SELECT should likely be a SELECT DISTINCT as there could be cases where you have duplicate rows in #tblFormula and you likely don't need duplicate results. If the rows had some uniquifier (an identity for example), then the WHERE clause on the dynamic SQL could be cleaner and it would handle duplicate rows better.

Well, tbh, I can see both of those responses. Some tweaks are needed for various aggregation type queries but it can work. While it works and can be done, this is so much more efficient through clr or from the presentation layer rather than sql.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQL_Hacker SSCommitted Points: 1728 More actions · Answer 9

@ Bmg002: +1
Yes, I agree that cursors are evil ( 😀 ), but they do have their uses occasionally. Very cool answer!!
It would be really cool to see how the performance degrades with millions (or, probably thousands) of rows to process in the formula table into the solution table. It definitely would because SQL doesn't handle RBAR very well. But, it would be fun to see that on a test system.

Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 10

SQLRNNR - Wednesday, August 23, 2017 11:17 AM
Well, tbh, I can see both of those responses. Some tweaks are needed for various aggregation type queries but it can work. While it works and can be done, this is so much more efficient through clr or from the presentation layer rather than sql.

Yes, I do agree that it isn't the best solution; it is like the hammer for a screw analogy above. But it does work for the sample data provided. The OP had asked "how would I do this in SQL?" not "what is the best way to do something like this? I was thinking SQL but there has to be a better option". I wasn't trying to provide them with the "best" solution, but I imagine that doing that in an application would be a little overkill as well. I mean, powershell could do it, C#, matlab, etc... There ARE better tools for solving something like this, but it was a fun thought exercise. I can't think of a situation (outside of education) where a requirement would come up where this was the only available solution though.

SQL_Hacker - Wednesday, August 23, 2017 11:25 AM
@ Bmg002: +1
Yes, I agree that cursors are evil ( 😀 ), but they do have their uses occasionally. Very cool answer!!
It would be really cool to see how the performance degrades with millions (or, probably thousands) of rows to process in the formula table into the solution table. It definitely would because SQL doesn't handle RBAR very well. But, it would be fun to see that on a test system.

I haven't tested it with large data sets, but I expect it to be linear growth in relation to time. There isn't any complex math happening, so off the top of my head (ignoring IO and possible tempdb growth), I'd expect it to run in O(n) time. Since it is all temporary tables, I don't see it doing any blocking or locking (well, except on the temp tables), so unless it does some weird self blocking, it should be O(n).
I was just looking at it again, and if you had millions of rows, having 2 temp tables storing nearly identical data feels wasted. I think it would be better to throw on an ID identity column on the first table and then the second would have 2 columns instead of 4 - result and parentID. Then join the 2 tables at the end. Reduces duplication. At least that is how I'd design it if I was going to throw this into production. Mind you, I'd only throw it into production if I had no other options for the calculations.
If it was a one-time thing for updating data in a table, then I'd probably let that sneak past into production where I work if it was a small data set OR I could run it after hours and have the system usable when production came back in.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Joe Torre SSChampion Points: 10248 More actions · Answer 11


CREATE TABLE [#tblFormula](C1 float, C2 float, Formula nvarchar(50));
INSERT INTO [#tblFormula] (C1, C2, [Formula])
VALUES (5, 1, 'C1+C2')
     , (7, 2.5, 'C1*C2')
     , (4.5, 8, 'C1/C2');

SELECT tf.C1
     , tf.C2
     , tf.Formula
     , CASE Substring(tf.Formula, 3, 1)
         WHEN '+' THEN tf.C1+tf.C2
         WHEN '*' THEN tf.C1*tf.C2
         WHEN '/' THEN tf.C1/tf.C2 END Result
FROM #tblFormula tf;
DROP TABLE #tblFormula;

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 12

imanalimi - Tuesday, August 22, 2017 8:49 PM
Hi Guys,
I am trying to calculate formula using SQL Server 2016.
How can I get the result of a Formula within a Select Statement like this:
CREATE TABLE #tblFormula (C1 INT, C2 INT,Formula NVARCHAR(50)) INSERT INTO #tblFormula (C1,C2,Formula) VALUES (5,1,'C1+C2') ,(7,2.5,'C1*C2') ,(4.5,8,'C1/C2'); SELECT C1,C2,Formula,'Result' AS [Result] FROM #tblFormula
DROP TABLE #tblFormula;
The Result would be similar to:
C1....C2....Formula....Result
5........1.....C1+C2........6
7........2.....C1*C2........14
4........8.....C1/C2........0.5
Many Thanks

One of my next book proposals is going to be on bad SQL. You missed the fundamental concept about RDBMS, namely that it is for data not for computation. We store data on our side of the wall, and we toss sets of data over the wall to the other layers of our tiered architecture, which do computations, formatting, and God knows what else because we don't care..

Perhaps your intent was to have a computed column? You can Google that. It is a standard SQL feature.

My interest is that I teach SQL and have for over 30 years, so I want to know why you were thinking this would be possible or valid. People do not make random mistakes. My guess is that your Lisp programmer who expects to be able to turn data, structure and computation into each other. Is that correct? This up a little weird. I'm used to seeing COBOL programmers who make characteristic mistakes about the nature of data from their language, but this is as I said weird.

Please post DDL and follow ANSI/ISO standards when asking for help.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 13

A few things about this:
1.) This would be very difficult to achieve in T-SQL
2.) Why would you want to do this? There are much better tools already available (e.g. Mathematica)
3.) T-SQL is a set-based language, NOT a procedural one. Just because you can use it that way doesn't make it procedural.
Steve
(aka sgmunson)
Smile Smile Smile
Health & Nutrition

Technically T-SQL is both procedural and declarative. Set-based is not in the conversation. Stylistically you can accomplish iterative or set-based with T-SQL.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

jcelko212 32090 - Wednesday, August 23, 2017 4:09 PM
imanalimi - Tuesday, August 22, 2017 8:49 PM
Hi Guys,
I am trying to calculate formula using SQL Server 2016.
How can I get the result of a Formula within a Select Statement like this:
CREATE TABLE #tblFormula (C1 INT, C2 INT,Formula NVARCHAR(50)) INSERT INTO #tblFormula (C1,C2,Formula) VALUES (5,1,'C1+C2') ,(7,2.5,'C1*C2') ,(4.5,8,'C1/C2'); SELECT C1,C2,Formula,'Result' AS [Result] FROM #tblFormula
DROP TABLE #tblFormula;
The Result would be similar to:
C1....C2....Formula....Result
5........1.....C1+C2........6
7........2.....C1*C2........14
4........8.....C1/C2........0.5
Many Thanks
One of my next book proposals is going to be on bad SQL. You missed the fundamental concept about RDBMS, namely that it is for data not for computation. We store data on our side of the wall, and we toss sets of data over the wall to the other layers of our tiered architecture, which do computations, formatting, and God knows what else because we don't care..
Perhaps your intent was to have a computed column? You can Google that. It is a standard SQL feature.
My interest is that I teach SQL and have for over 30 years, so I want to know why you were thinking this would be possible or valid. People do not make random mistakes. My guess is that your Lisp programmer who expects to be able to turn data, structure and computation into each other. Is that correct? This up a little weird. I'm used to seeing COBOL programmers who make characteristic mistakes about the nature of data from their language, but this is as I said weird.

I'm sorry, but stop picking on us old COBOL programmers. I for one made the shift without recreating COBOL code in T-SQL.