Stored Procedure for SQL Server

The Real-time Data Validity determination rule of INSQL requires a flexible definition. I use the standard SQL expression to define the rule in the string mode, and parse and output the result through the stored procedure to determine the result. For specific code, see the following content;

 

Code
-- ===================================================== ======
-- Author: SUIFEI
-- Create date: 2008-8-5
-- Description: Determination rule
-- ===================================================== ======
Alter procedure [dbo]. [MP_JC_DATAISVALID]
@ EXPS varchar (4096), -- Expression
@ RESULT int = null OUT -- output parameter: 1 indicates valid, 0 indicates invalid
AS
BEGIN
/*
Call Demo:
Expression: [YT601_PLAN_YP] BETWEEN 6500 AND 7000, [WQ4_EXPORT_TEMP]> = 30
Explanation: The tag data of YT601_PLAN_YP must be between 6500 and 7000, and the data of WQ4_EXPORT_TEMP must be greater than or equal to 20.

DECLARE @ RESULT INT
EXEC MP_JC_DATAISVALID '[YT601_PLAN_YP] BETWEEN 6500 AND 7000, [WQ4_EXPORT_TEMP]> = 30', @ RESULT OUT
SELECT @ RESULT
*/
/*
Defined data structure: tag Name, parameter code, expression (standard SQL expression)
TAGNAME, PARAMETERCODE, EXPRESSION
A_1 1001 NULL
A2 1001 [A1]> 100, [A2] BETWEEN 98 AND 102
A3 1001 NULL
*/
SET NOCOUNT ON
-- Enable direct debugging in this sentence: SET @ EXPS = '[YT601_PLAN_YP] BETWEEN 6500 AND 7000, [WQ4_EXPORT_TEMP]> = 30'
DECLARE @ exp varchar (2048)
DECLARE @ R INT
DECLARE @ expsql varchar (4096)
 
SET @ R = 0
SET @ EXPS = LTRIM (RTRIM (@ EXPS ))
SET @ EXPSQL =''
 
IF (RIGHT (RTRIM (@ EXPS), 1) <> ',') SET @ EXPS = @ EXPS + ','
 
Select '[' + TAGNAME + ']' as tagname, value into # tmp from live -- LIVE table is a real-time data table
UPDATE # tmp set @ EXPS = REPLACE (@ EXPS, TAGNAME, VALUE)
Where charindex (TAGNAME, @ EXPS) <> 0
Drop table # TMP
 
Create table # EXPRESSION (result int)
 
WHILE (CHARINDEX (',', @ EXPS) <> 0)
BEGIN
SET @ EXP = SUBSTRING (@ EXPS, 1, CHARINDEX (',', @ EXPS)-1)
SET @ EXPS = SUBSTRING (@ EXPS, CHARINDEX (',', @ EXPS) + 1, LEN (@ EXPS ))
 
SET @ EXPSQL = 'insert INTO # expression' +
'Select (case when ('+
@ EXP +
') THEN 1 ELSE 0 END )'
EXEC (@ EXPSQL)
PRINT @ EXPSQL
END
SELECT @ RESULT = MIN (RESULT) FROM # EXPRESSION
Drop table # EXPRESSION
PRINT @ RESULT
RETURN @ RESULT;
END