Data lineage for Stored Procedure in SQLFlow

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

Basic syntax 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 @var INT
AS
BEGIN
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. As tool of the Industry leading companies, it provides SQL-related data lineage analysis features. You can try it yourself on: https://sqlflow.gudusoft.com