Search code examples
sqlstored-proceduressnowflake-cloud-data-platform

Snowflake scripting: writing stored procedure using SQL

CREATE OR REPLACE PROCEDURE CITY_INFO(city_name VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
BEGIN
    -- validate city_name
    IF city_name NOT IN ('SYD', 'LDN', 'NYC', 'BER') THEN
        RETURN 'Invalid CITY NAME.';
    END IF;

 -- The rest of the code goes here...

END;

I'm creating stored procedure in Snowflake using SQL (Snowflake scripting). I want to ensure that city_name passed only belongs to these 4 cities. However, this doesn't seem to be working. Any help highly appreciated.

Solution

  • The condition must be enclosed with ():

    BEGIN
    -- validate city_name
    IF (city_name NOT IN ('SYD', 'LDN', 'NYC', 'BER')) THEN
        RETURN 'Invalid CITY NAME.';
    END IF;

 -- The rest of the code goes here...

END;