Skip to content
No results
We use cookies to ensure that we give you the best experience on our website.

# Find all variables in SQL script

It’s quite easy to use General SQL Parser to find out all SQL variables in a SQL script. It will not only find variables in declare statement but also find variables where it was used.

This is a query includes SQL variables

    DECLARE @ProductId BIGINT
    DECLARE @OTSProductId BIGINT

    SET @ProductId = 35768
    SET @OTSProductId = @ProductId

    IF NOT EXISTS(SELECT ProductId
                  FROM   PRODUCT
                  WHERE  PRODUCTID = @ProductId)
      BEGIN
          INSERT INTO [Product]
                      ([ProductId],
                       [Name])
          SELECT @ProductId,
                 'Please see the insert in this bill ...'
      END
    ELSE
      BEGIN
          PRINT REPLACE('product  already exists', '', @ProductId)
      END

All SQL varaibles found in that SQL script

    @ProductId
    @OTSProductId
    @ProductId
    @OTSProductId
    @ProductId
    @ProductId
    @ProductId
    @ProductId

Even more, General SQL Parser can find variable used throughout the whole stored procedure to extract a lineage.

Take this SQL for example

    CREATE PROCEDURE STGTRSODS.P_LOAD_DC_UNIVL_PLAN_R2_R3
    ( IN in_cycle_id CHAR(2))
        RESULT SETS 1
        LANGUAGE SQL

    P1: BEGIN
    ------------------------------------------------------------------------
    -- DECLARE SECTION
    ------------------------------------------------------------------------
    DECLARE SQLSTATE CHAR(5);
    DECLARE wk_sqlstate             CHAR(5) DEFAULT '00000';
    DECLARE cs_VALUE            CHARACTER(100);
    DECLARE wk_fetch_status          CHAR(5) DEFAULT '00000';
    DECLARE cs_PLAN_NUM             CHARACTER(6);
    DECLARE cs_SUB_PLAN             CHARACTER(6);

    --CLARE cs_if_cursor CURSOR WITH HOLD WITH RETURN FOR cs_if_cursor;

    SET  wk_stg_cfe_r3 = ACCTLOAD.F_GET_ETL_TABLE(in_cycle_id,'ACCTLOAD.T_STG_CFE_$$$_R3');
    SET  wk_stg_cfe_r2 = ACCTLOAD.F_GET_ETL_TABLE(in_cycle_id,'ACCTLOAD.T_STG_CFE_$$$_R2');

    FETCH cs_if_cursor
        INTO cs_PLAN_NUM, cs_SUB_PLAN, cs_KEY_WORD, cs_VALUE;

    WHILE (wk_fetch_status = '00000') DO
        CASE cs_KEY_WORD
            WHEN 'DSHR PARTICIPATING DATASHARE INDICATOR' THEN
                IF EXISTS(SELECT 1 FROM SESSION.GLBLTMP_R3_R2 WHERE PLAN_NUM = cs_PLAN_NUM AND SUB_PLAN = cs_SUB_PLAN) THEN
                    UPDATE SESSION.GLBLTMP_R3_R2
                        SET DSHR_PARTICIPATING_IND = SUBSTR(cs_VALUE,1,1)
                     WHERE PLAN_NUM = cs_PLAN_NUM
                       AND SUB_PLAN = cs_SUB_PLAN;
                ELSE
                    INSERT INTO SESSION.GLBLTMP_R3_R2 (PLAN_NUM,SUB_PLAN, DSHR_PARTICIPATING_IND, DSHR_FUNCTION)
                    VALUES (cs_PLAN_NUM,cs_SUB_PLAN, SUBSTR(cs_VALUE,1,1), NULL);
                END IF;

            WHEN 'DSHR DATASHARE FUNCTION' THEN
                IF EXISTS(SELECT 1 FROM SESSION.GLBLTMP_R3_R2 WHERE PLAN_NUM = cs_PLAN_NUM AND SUB_PLAN = cs_SUB_PLAN) THEN
                    UPDATE SESSION.GLBLTMP_R3_R2
                        SET DSHR_FUNCTION = SUBSTR(cs_VALUE,1,1)
                     WHERE PLAN_NUM = cs_PLAN_NUM
                       AND SUB_PLAN = cs_SUB_PLAN;
                ELSE
                    INSERT INTO SESSION.GLBLTMP_R3_R2 (PLAN_NUM,SUB_PLAN, DSHR_PARTICIPATING_IND, DSHR_FUNCTION)
                    VALUES (cs_PLAN_NUM,cs_SUB_PLAN, NULL, SUBSTR(cs_VALUE,1,1));
                END IF;

        END CASE;
    END WHILE;

    END P1

Analyzed result will like this:

    procedure name: STGTRSODS.P_LOAD_DC_UNIVL_PLAN_R2_R3

    Variable: wk_sqlstate         , Type: CHAR(5)
    Variable: cs_VALUE            , Type: CHARACTER(100)
    Variable: wk_fetch_status     , Type: CHAR(5)
    Variable: cs_PLAN_NUM         , Type: CHARACTER(6)
    Variable: cs_SUB_PLAN         , Type: CHARACTER(6)

    variable: cs_VALUE
    update table: SESSION.GLBLTMP_R3_R2
    column: DSHR_PARTICIPATING_IND
    value: SUBSTR(cs_VALUE,1,1)

    variable: cs_PLAN_NUM
    insert table: SESSION.GLBLTMP_R3_R2
    column: PLAN_NUM
    insert value: cs_PLAN_NUM

    variable: cs_SUB_PLAN
    insert table: SESSION.GLBLTMP_R3_R2
    column: SUB_PLAN
    insert value: cs_SUB_PLAN

    variable: cs_VALUE
    insert table: SESSION.GLBLTMP_R3_R2
    column: DSHR_PARTICIPATING_IND
    insert value: SUBSTR(cs_VALUE,1,1)

    variable: cs_VALUE
    update table: SESSION.GLBLTMP_R3_R2
    column: DSHR_FUNCTION
    value: SUBSTR(cs_VALUE,1,1)

    variable: cs_PLAN_NUM
    insert table: SESSION.GLBLTMP_R3_R2
    column: PLAN_NUM
    insert value: cs_PLAN_NUM

    variable: cs_SUB_PLAN
    insert table: SESSION.GLBLTMP_R3_R2
    column: SUB_PLAN
    insert value: cs_SUB_PLAN

    variable: cs_VALUE
    insert table: SESSION.GLBLTMP_R3_R2
    column: DSHR_FUNCTION
    insert value: SUBSTR(cs_VALUE,1,1)

Download this demo: C# version