IN

Transact-SQL subqueries using IN statement transformation details

The IN operator checks if an expression is included in the values returned by a subquery.

Source Code

CREATE PROCEDURE dbo.SP_IN_EXAMPLE
AS
	DECLARE @results as VARCHAR(50);

	SELECT @results = COUNT(*) FROM TABLE1

	IF @results IN (1,2,3)
		SELECT 'is IN';
	ELSE
		SELECT 'is NOT IN';
	
	return
GO

-- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE dbo.SP_IN_EXAMPLE
GO
                              

Expected Code

CREATE OR REPLACE PROCEDURE "SP_IN_SAMPLE"()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
	var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE = 0, SQLSTATE = '00000', ERROR_HANDLERS, NUM_ROWS_AFFECTED, INTO;
	var formatDate = (arg) => (new Date(arg - (arg.getTimezoneOffset() * 60000))).toISOString().slice(0,-1);
	
	var fixBind = function (arg) {
		arg = arg == undefined ? null : arg instanceof Date ? formatDate(arg) : arg;
		return arg;
	};
	
	var EXEC = (stmt,binds = [],noCatch = false,catchFunction = null) => {
		try {
			binds = binds ? binds.map(fixBind) : binds;
			_RS = snowflake.createStatement({
				sqlText : stmt,
				binds : binds
			});
			_ROWS = _RS.execute();
			ROW_COUNT = _RS.getRowCount();
			NUM_ROWS_AFFECTED = _RS.getNumRowsAffected();
			if (INTO) return {
				INTO : function () {
				return INTO();
				}
			};
		} catch(error) {
			MESSAGE_TEXT = error.message;
			SQLCODE = error.code;
			SQLSTATE = error.state;
			var msg = `ERROR CODE: ${SQLCODE} SQLSTATE: ${SQLSTATE} MESSAGE: ${MESSAGE_TEXT}`;
			if (catchFunction) catchFunction(error);
			if (!noCatch && ERROR_HANDLERS) ERROR_HANDLERS(error); else throw new Error(msg);
		}
	};

	EXEC(`SELECT COUNT(*) FROM (Select COL1 FROM TABLE1)`,[]);
    
	_ROWS.next();
    
	if ([1,2,3].includes(3))
		return "is IN";
    else
		return "is NOT IN"; 
$$;

CALL SP_IN_SAMPLE();