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

# Data lineage for Stored Procedure in SQLFlow

- leo gu
- August 3, 2023
- Uncategorized

A stored procedure is a prepared SQL code which can be reused again and again. It helps you to encapsulate a serie of SQL statements and makes easy to reuse your SQL code with different input parameters.

Basic sytax for Stored Procedure could be:

    CREATE PROCEDURE procedure_name
       @parameter1 datatype,
       @parameter2 datatype
    AS
    BEGIN
       -- SQL statements to be executed
    END

As an automated data lineage tool, SQLFlow is also able to analyze Stored Procedure and generates relative data lineage.

Let’s consider the following example:

    CREATE PROCEDURE dbo.udfProductInYear
    AS
    BEGIN
    DECLARE @var as INT
    SELECT
    product_name,
    model_year,
    list_price
    FROM
    production.products
    WHERE
    model_year = @var;
    END
    GO

    INSERT into dbo.targetTable(product_name, model_year, list_price) exec dbo.udfProductInYear @var=2023

We just created a procedure called `dbo.udfProductInYear` and it returns `product_name`, `model_year` and `list_price` which are retrieved from `production.products` when `model_year` equals to the input.

Ideally, we should have the following data lineage based on the above example:

The dbo.targetTable contains three fields. They are generated from the dbo.udfProductInYear procedure and from production.products where model_year equals to 2023.

Paste the above SQL statements into SQLFlow and we got:

Gudu SQLFlow is widely used in the data governance tool of the Industry leading companies providing SQL-related data lineage analysis features. You can try it yourself on: https://sqlflow.gudusoft.com

### Newsletter Updates

Enter your email address below to subscribe to our newsletter

I accept the Privacy Policy

### Related Posts

#### Ingest Oracle metadata on Datahub

- July 14, 2023

#### Ingest Microsoft SQL Server metadata on Datahub

- July 14, 2023

#### Ingest PostgreSQL metadata on Datahub

- July 12, 2023