How to check if a set of conditions in a string is true in SQL-CodePudding

I have an expression:

DECLARE @Conditions NVARCHAR(MAX) = '200>100 AND YEAR(GETDATE())=2022'

I want to check if the expression is true.

CodePudding user response：

To execute a string as a SQL statement from within T-SQL, use sp_executesql

In your case you'd want to make use of output parameters and embed your code snippet in a larger statement such as SELECT @outputParam = CASE WHEN <your code snippet> THEN 1 ELSE 0 END (As T-SQL doesn't have a boolean data type.)

DECLARE @Conditions NVARCHAR(MAX) = '200>100 AND YEAR(GETDATE())=2022';

DECLARE @SQLString NVARCHAR(MAX);

DECLARE @SQLResult INT;

SET @SQLString = CONCAT(
    N'SELECT @Result = CASE WHEN ',
    @Conditions,
    N' THEN 1 ELSE 0 END;'
)

EXECUTE sp_executesql  
    @SQLString  
   ,N'@Result INT OUTPUT'
   ,@Result = @SQLResult OUTPUT;  

-- This SELECT statement returns the value of the OUTPUT parameter.  
SELECT @SQLResult;

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5697ea4d14201e44d0e8888abbc6037b

EDIT:

Avoiding CASE expressions by initialising the result to 0 (fail) and only setting it to 1 (success) if the conditions are true.

DECLARE @Conditions NVARCHAR(MAX) = '200>100 AND YEAR(GETDATE())=2022';

DECLARE @SQLString NVARCHAR(MAX);

DECLARE @SQLResult INT = 0;

SET @SQLString = CONCAT(
    N'SELECT @Result = 1 WHERE ',
    @Conditions
)

EXECUTE sp_executesql  
    @SQLString  
   ,N'@Result INT OUTPUT'
   ,@Result = @SQLResult OUTPUT;  

-- This SELECT statement returns the value of the OUTPUT parameter.  
SELECT @SQLResult;

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=3e375c464f1eab2e652bc446a4c7484f