Post reply

Eval function TSql

  • August 9, 2018 at 8:14 am

    #357303

    is there a TSQL function that allows to validate an operation? Example:

    Select (1 = 0) should return false while Select (1 = 1) should return false

    or

    Select ('a' = 'b') should return false while Select ('a' = 'a') should return false

    thanks in advance, and sorry for my bad english

    Marco

  • August 9, 2018 at 8:44 am

    #2001037

    Use a CASE of IIF expression.

    SELECT IsEqual = CASE WHEN a=b THEN 1 ELSE 0 END;

    ... or ...

    SELECT IsEqual = IIF(a=b,1,0);

    As a bit of a side bar, when you're learning a new language, one of the best things you can do is to learn about as many of the functions and "comparators" intrinsic to the language as possible because that's where the real power and performance lies.  Learn how and what to look for in the search engines and then study the answers rather than just use them.  For example, your next move would be to search for "CASE expressions in SQL Server" and "Functions in SQL Server".

    Here's a link to kick start your learning.
    https://docs.microsoft.com/en-us/sql/t-sql/functions/functions?view=sql-server-2017

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 9, 2018 at 9:05 am

    #2001049

    Jeff Moden - Thursday, August 9, 2018 8:44 AM

    Use a CASE of IIF expression.

    SELECT IsEqual = CASE WHEN a=b THEN 1 ELSE 0 END;

    ... or ...

    SELECT IsEqual = IIF(a=b,1,0);

    As a bit of a side bar, when you're learning a new language, one of the best things you can do is to learn about as many of the functions and "comparators" intrinsic to the language as possible because that's where the real power and performance lies.  Learn how and what to look for in the search engines and then study the answers rather than just use them.  For example, your next move would be to search for "CASE expressions in SQL Server" and "Functions in SQL Server".

    Here's a link to kick start your learning.
    https://docs.microsoft.com/en-us/sql/t-sql/functions/functions?view=sql-server-2017

    right, but it's not enough for me. the posted example was too simple. I have a string like this:
    '0 = -1 and' C311 '<>' C124 '
    If I run the command

    Select IIF ((0 = -1 and 'C311' <> 'C124'), 1.0)
    Everything is alright

    But if I do an exec
    DECLARE @ssql VARCHAR (8000)
    SET @ ssql = 'Select top 1 IIF ((' + '0 = -1 and' 'C311' '<>' 'C124' '' + '), 1.0)'
    PRINT @ssql
    EXEC @ssql

    Sql gives me this error
    Could not find stored procedure 'Select top 1 IIF ((0 = -1 and' C311 '<>' C124 '), 1.0)'.

    My problem is that the expression to be validated is contained in a variable varchar

  • August 9, 2018 at 9:11 am

    #2001053

    marco.bugada - Thursday, August 9, 2018 9:05 AM

    right, but it's not enough for me. the posted example was too simple. I have a string like this:
    '0 = -1 and' C311 '<>' C124 '
    If I run the command

    Select IIF ((0 = -1 and 'C311' <> 'C124'), 1.0)
    Everything is alright

    But if I do an exec
    DECLARE @ssql VARCHAR (8000)
    SET @ ssql = 'Select top 1 IIF ((' + '0 = -1 and' 'C311' '<>' 'C124' '' + '), 1.0)'
    PRINT @ssql
    EXEC @ssql

    Sql gives me this error
    Could not find stored procedure 'Select top 1 IIF ((0 = -1 and' C311 '<>' C124 '), 1.0)'.

    My problem is that the expression to be validated is contained in a variable varchar

    You're missing the parenthesis to run it as dynamic SQL. You also have many syntax problems in your code.

    DECLARE @ssql VARCHAR (8000);
    SET @ssql = 'Select top 1 IIF ((' + '0 = -1 and ''C311'' <> ''C124''' + '), 1, 0)';
    PRINT @ssql;
    EXEC (@ssql);
    GO
    --Even better use this to be able to parametrize the queries
    DECLARE @ssql NVARCHAR (MAX);
    SET @ssql = N'Select top 1 IIF ((' + '0 = -1 and ''C311'' <> ''C124''' + '), 1, 0)';
    PRINT @ssql;
    EXEC sp_executesql @ssql;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • August 10, 2018 at 12:39 pm

    #2001296

    marco.bugada - Thursday, August 9, 2018 8:14 AM

    is there a TSQL function that allows to validate an operation? Example:

    Select (1 = 0) should return false while Select (1 = 1) should return false

    or

    Select ('a' = 'b') should return false while Select ('a' = 'a') should return false

    thanks in advance, and sorry for my bad english

    Marco

    >> is there a TSQL function that allows to validate an operation? Example:
    Select (1 = 0) should return FALSE while Select (1 = 1) should return TRUE
    .. sorry for my bad english <<

    I'm about to give you a pedantic answer. You might want to get a cigarette or a beer 🙂

    In ANSI/ISO Standard SQL, there is a predicate:

    <search condition> IS [TRUE | FALSE | UNKNOWN]

    But this predicate is not part of T–SQL or a lot of other SQL products. I tell people that, SQL is a predicate language. I mean that we test whether something is true, false or unknown, rather than setting it is a flag. We actually have two kinds of logic in this language. In the DML, {FALSE, UNKNOWN} test the same, but in the DDL you get the "benefit of the doubt" and {TRUE, UNKNOWN} test the same..

    I don't think there is another language that has NULLs, so I would recommend that you spend some time learning the rules for them. It is complicated., But if you could learn English, then this insanity should not be too bad :-).

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

  • August 10, 2018 at 1:19 pm

    #2001305

    jcelko212 32090 - Friday, August 10, 2018 12:39 PM

    marco.bugada - Thursday, August 9, 2018 8:14 AM

    is there a TSQL function that allows to validate an operation? Example:

    Select (1 = 0) should return false while Select (1 = 1) should return false

    or

    Select ('a' = 'b') should return false while Select ('a' = 'a') should return false

    thanks in advance, and sorry for my bad english

    Marco

    >> is there a TSQL function that allows to validate an operation? Example:
    Select (1 = 0) should return FALSE while Select (1 = 1) should return TRUE
    .. sorry for my bad english <<

    I'm about to give you a pedantic answer. You might want to get a cigarette or a beer 🙂

    In ANSI/ISO Standard SQL, there is a predicate:

    <search condition> IS [TRUE | FALSE | UNKNOWN]

    But this predicate is not part of T–SQL or a lot of other SQL products. I tell people that, SQL is a predicate language. I mean that we test whether something is true, false or unknown, rather than setting it is a flag. We actually have two kinds of logic in this language. In the DML, {FALSE, UNKNOWN} test the same, but in the DDL you get the "benefit of the doubt" and {TRUE, UNKNOWN} test the same..

    I don't think there is another language that has NULLs, so I would recommend that you spend some time learning the rules for them. It is complicated., But if you could learn English, then this insanity should not be too bad :-).

    Always knew you were arrogant, Mr. Celko, but you just showed me how much.

  • August 10, 2018 at 7:26 pm

    #2001331

    Lynn Pettis - Friday, August 10, 2018 1:19 PM

    jcelko212 32090 - Friday, August 10, 2018 12:39 PM

    marco.bugada - Thursday, August 9, 2018 8:14 AM

    Always knew you were arrogant, Mr. Celko, but you just showed me how much.

    Explain. Definition of arrogance for Students. : a person's sense of his or her own importance that shows itself in a proud and insulting way. Who was insulted? How did I promote myself? Hell, I did not even plug my books:) I just posted industry standards and some of X3H2  experience. What did you post to help him?

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

  • August 11, 2018 at 12:23 am

    #2001338

    Luis Cazares - Thursday, August 9, 2018 9:11 AM

    marco.bugada - Thursday, August 9, 2018 9:05 AM

    right, but it's not enough for me. the posted example was too simple. I have a string like this:
    '0 = -1 and' C311 '<>' C124 '
    If I run the command

    Select IIF ((0 = -1 and 'C311' <> 'C124'), 1.0)
    Everything is alright

    But if I do an exec
    DECLARE @ssql VARCHAR (8000)
    SET @ ssql = 'Select top 1 IIF ((' + '0 = -1 and' 'C311' '<>' 'C124' '' + '), 1.0)'
    PRINT @ssql
    EXEC @ssql

    Sql gives me this error
    Could not find stored procedure 'Select top 1 IIF ((0 = -1 and' C311 '<>' C124 '), 1.0)'.

    My problem is that the expression to be validated is contained in a variable varchar

    You're missing the parenthesis to run it as dynamic SQL. You also have many syntax problems in your code.

    DECLARE @ssql VARCHAR (8000);
    SET @ssql = 'Select top 1 IIF ((' + '0 = -1 and ''C311'' <> ''C124''' + '), 1, 0)';
    PRINT @ssql;
    EXEC (@ssql);
    GO
    --Even better use this to be able to parametrize the queries
    DECLARE @ssql NVARCHAR (MAX);
    SET @ssql = N'Select top 1 IIF ((' + '0 = -1 and ''C311'' <> ''C124''' + '), 1, 0)';
    PRINT @ssql;
    EXEC sp_executesql @ssql;

    We can improve this a little bit by using the expression to be evaluated as a search condition
    😎

    DECLARE @EXPRESSION NVARCHAR(100) = N'''A''=''A''';
    DECLARE @EVAL_STR NVARCHAR(MAX) = REPLACE(N'
    WITH BASE_DATA AS
    (
      SELECT 1 AS RES
    )
    SELECT
      COUNT(BD.RES) AS IS_EQUAL
    FROM  BASE_DATA BD
    WHERE ({{@EXPRESSION}});',N'{{@EXPRESSION}}',@EXPRESSION);

    EXEC SP_EXECUTESQL @EVAL_STR;

Viewing 8 posts - 1 through 7 (of 7 total)

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