{"id":2923,"date":"2023-08-03T09:24:27","date_gmt":"2023-08-03T14:24:27","guid":{"rendered":"https:\/\/www.dpriver.com\/blog\/?p=2923"},"modified":"2023-08-03T09:26:31","modified_gmt":"2023-08-03T14:26:31","slug":"data-lineage-for-stored-procedure-in-sqlflow","status":"publish","type":"post","link":"https:\/\/www.dpriver.com\/blog\/2023\/08\/data-lineage-for-stored-procedure-in-sqlflow\/","title":{"rendered":"Data lineage for Stored Procedure in SQLFlow"},"content":{"rendered":"\n

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.<\/p>\n\n\n\n

Basic sytax for Stored Procedure could be:<\/p>\n\n\n\n

    CREATE PROCEDURE procedure_name\n   @parameter1 datatype,\n   @parameter2 datatype\nAS\nBEGIN\n   -- SQL statements to be executed\nEND <\/code><\/pre>\n\n\n\nAs an automated data lineage tool, SQLFlow is also able to analyze Stored Procedure and generates relative data lineage.<\/p>\n\n\n\nLet’s consider the following example:<\/p>\n\n\n\nCREATE PROCEDURE dbo.udfProductInYear \nAS\nBEGIN\nDECLARE @var as INT\nSELECT \nproduct_name,\nmodel_year,\nlist_price\nFROM\nproduction.products\nWHERE\nmodel_year = @var;\nEND        \nGO\n\nINSERT into dbo.targetTable(product_name, model_year, list_price) exec dbo.udfProductInYear @var=2023<\/code><\/pre>\n\n\n\nWe just created a procedure called dbo.udfProductInYear <\/code>and it returns product_name<\/code>, model_year<\/code> and list_price<\/code> which are retrieved from production.products<\/code> when model_year<\/code> equals to the input.<\/p>\n\n\n\nIdeally, we should have the following data lineage based on the above example:<\/p>\n\n\n\n<\/figure>\n\n\n\nThe dbo.targetTable contains three fields. They are generated from the dbo.udfProductInYear procedure and from production.products where model_year equals to 2023. <\/p>\n\n\n\nPaste the above SQL statements into SQLFlow and we got:<\/p>\n\n\n\n<\/figure>\n\n\n\nGudu 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<\/p>\n","protected":false},"excerpt":{"rendered":"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: 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: 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\u2026<\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nData lineage for Stored Procedure in SQLFlow<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n