SnowConvert AI - SQL Server-Azure Synapse - SELECT¶

SELECT¶

Translation reference for SELECT statement inside procedures in Transact-SQL.

Description¶

Snowflake SQL supports returning tables as a return type for Stored Procedures, but unlike Transact-SQL, Snowflake does not support returning multiple resultsets in the same procedure. For this scenario, all the query IDs are stored in a temporary table and returned as an array.

Sample Source Patterns¶

The following example details the transformation when there is only one SELECT statement in the procedure.

Transact-SQL¶

Single Resultset¶

CREATE PROCEDURE SOMEPROC()
AS
BEGIN
        SELECT * from AdventureWorks.HumanResources.Department;
END

Output¶

Snowflake SQL¶

Single Resultset¶

CREATE OR REPLACE PROCEDURE SOMEPROC ()
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
        DECLARE
                ProcedureResultSet RESULTSET;
        BEGIN
                ProcedureResultSet := (
                SELECT
                        *
                from
                        AdventureWorks.HumanResources.Department);
                RETURN TABLE(ProcedureResultSet);
        END;
$$;

Output¶

The following example details the transformation when there are many SELECT statements in the procedure.

Transact-SQL¶

Multiple Resultset¶

 CREATE PROCEDURE SOMEPROC()
AS
BEGIN
        SELECT * from AdventureWorks.HumanResources.Department;
        SELECT * from AdventureWorks.HumanResources.Shift;
END

Output¶

Snowflake SQL¶

Single Resultset¶

CREATE OR REPLACE PROCEDURE SOMEPROC ()
RETURNS ARRAY
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
        DECLARE
                ProcedureResultSet1 VARCHAR;
                ProcedureResultSet2 VARCHAR;
                return_arr ARRAY := array_construct();
        BEGIN
                ProcedureResultSet1 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
                CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet1) AS
                        SELECT
                                *
                        from
                                AdventureWorks.HumanResources.Department;
                return_arr := array_append(return_arr, :ProcedureResultSet1);
                ProcedureResultSet2 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
                CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet2) AS
                        SELECT
                                *
                        from
                                AdventureWorks.HumanResources.Shift;
                return_arr := array_append(return_arr, :ProcedureResultSet2);
                --** SSC-FDM-0020 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
                RETURN return_arr;
        END;
$$;

Output¶

Known Issues¶

1. The query results should be accessed by using the IDs returned by the Stored Procedure

Related EWIs¶

1. SSC-FDM-0020: Multiple result sets are returned in temporary tables.

TOP¶

Description¶

Limits the rows returned in a query result set to a specified number of rows or percentage of rows. When you use TOP with the ORDER BY clause, the result set is limited to the first N number of ordered rows. Otherwise, TOP returns the first N number of rows in an undefined order. Use this clause to specify the number of rows returned from a SELECT statement. Or, use TOP to specify the rows affected by an INSERT, UPDATE, MERGE, or DELETE statement. (Transact-SQL TOP documentation (https://learn.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql?view=sql-server-ver16))

Syntax in Transact-SQL¶

 TOP (expression) [PERCENT] [ WITH TIES ]

Syntax in Snowflake¶

 TOP <n>

Sample Source Patterns¶

To execute correctly the following samples it is required run the next CREATE TABLE statement:

Transact-SQL¶

 CREATE TABLE Cars(
    Model VARCHAR(15),
    Price MONEY,
    Color VARCHAR(10)
);

INSERT Cars VALUES ('sedan', 10000, 'red'),
('convertible', 15000, 'blue'),
('coupe', 20000, 'red'),
('van', 8000, 'blue'),
('sub', 8000, 'green');

Snowflake¶

 CREATE OR REPLACE TABLE Cars (
    Model VARCHAR(15),
    Price NUMBER(38, 4),
    Color VARCHAR(10)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;

INSERT INTO Cars VALUES ('sedan', 10000, 'red'),
('convertible', 15000, 'blue'),
('coupe', 20000, 'red'),
('van', 8000, 'blue'),
('sub', 8000, 'green');

Common Case¶

Transact-SQL¶

Query¶

 SELECT TOP(1) Model, Color, Price
FROM Cars
WHERE Color = 'red'

Result¶

Snowflake¶

Query¶

 SELECT
TOP 1
Model,
Color,
Price
FROM
Cars
WHERE
Color = 'red';

Result¶

TOP using PERCENT¶

Transact-SQL¶

Query¶

 SELECT TOP(50)PERCENT Model, Color, Price FROM Cars

Result¶

Snowflake¶

Query¶

SELECT
TOP 50 !!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE 'TOP PERCENT' CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
Model,
Color,
Price
FROM
Cars;

Result¶

TOP WITH TIES¶

Transact-SQL¶

Query¶

 SELECT TOP(50)PERCENT WITH TIES Model, Color, Price FROM Cars ORDER BY Price;

Result¶

Snowflake¶

Query¶

 SELECT
 TOP 50 !!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE 'TOP PERCENT AND WITH TIES' CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
  Model,
  Color,
  Price
 FROM
  Cars
 ORDER BY Price;

Result¶

Known Issues¶

1. PERCENT argument is not supported by Snowflake¶

Since the PERCENT argument is not supported by Snowflake it is being removed from the TOP clause and a warning is being added. Functional equivalence mismatches in the results could happen.

2. WITH TIES argument is not supported by Snowflake¶

Since the WITH TIES argument is not supported by Snowflake it is being removed from the TOP clause and a warning is being added. Functional equivalence mismatches in the results could happen.

Related EWIs¶

1. SSC-EWI-0040: Statement Not Supported.

Window-function ORDER BY NULL Ordering¶

Description¶

The T-SQL family treats NULL as the smallest value, so ORDER BY x ASC sorts NULLs first and ORDER BY x DESC sorts NULLs last. Snowflake’s default treats NULL as the largest value, which silently inverts the position of NULLs and changes window-function ranks/values for any partition that contains NULLs in the order key.

To preserve T-SQL family semantics, SnowConvert AI emits an explicit NULLS FIRST or NULLS LAST clause on every ORDER BY item inside an OVER (...) window-function clause that does not already carry one:

The rewrite is idempotent: items that already specify a NULLS … clause are preserved verbatim, including caller overrides such as ORDER BY x ASC NULLS LAST.

Sample Source Patterns¶

Input Code:¶

Transact-SQL¶

SELECT
  Name,
  DENSE_RANK() OVER (PARTITION BY Region ORDER BY SalesTotal DESC) AS RankInRegion,
  ROW_NUMBER() OVER (ORDER BY HireDate) AS HireOrder
FROM Employees;

Output Code:¶

Snowflake¶

SELECT
  Name,
  DENSE_RANK() OVER (PARTITION BY Region ORDER BY SalesTotal DESC NULLS LAST) AS RankInRegion,
  ROW_NUMBER() OVER (ORDER BY HireDate NULLS FIRST) AS HireOrder
FROM Employees;

Known Issues¶

1. Scope is limited to window-function OVER (...) clauses. Statement-level ORDER BY (outside OVER) and WITHIN GROUP (ORDER BY ...) carry the same NULL-ordering divergence but are intentionally not rewritten. If the result of a SELECT ... ORDER BY x query depends on NULL position, add an explicit NULLS FIRST / NULLS LAST clause manually or set the session parameter DEFAULT_NULL_ORDERING.
2. Two unsupported functions do not receive the rewrite: EXP_WEIGHTED_AVG and WEIGHTED_AVG. Their EWI-flagging replacer wraps the function before the inner ORDER BY is visited, so the NULLS clause is not added. Both already require manual rework by the user (they emit SSC-EWI-0073), so the missing clause is moot in practice.