{"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-09T08:22:31","modified_gmt":"2023-08-09T13:22: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<p>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.<\/p>\n\n\n\n<p>Basic syntax for Stored Procedure could be:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE PROCEDURE procedure_name\n   @parameter1 datatype,\n   @parameter2 datatype\nAS\nBEGIN\n   -- SQL statements to be executed\nEND <\/code><\/pre>\n\n\n\n<p>As an automated data lineage tool, <a href=\"https:\/\/sqlflow.gudusoft.com\/#\/\" target=\"_blank\" rel=\"noreferrer noopener\">SQLFlow<\/a> is also able to analyze Stored Procedure and generates relative data lineage.<\/p>\n\n\n\n<p>Let&#8217;s consider the following example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE PROCEDURE dbo.udfProductInYear @var INT\nAS\nBEGIN\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\n<p>We just created a procedure called <code>dbo.udfProductInYear <\/code>and it returns <code>product_name<\/code>, <code>model_year<\/code> and <code>list_price<\/code> which are retrieved from <code>production.products<\/code> when <code>model_year<\/code> equals to the input.<\/p>\n\n\n\n<p>Ideally, we should have the following data lineage based on the above example:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" loading=\"lazy\" width=\"1024\" height=\"264\" src=\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2023\/08\/image-1024x264.png\" alt=\"\" class=\"wp-image-2924\" srcset=\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2023\/08\/image-1024x264.png 1024w, https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2023\/08\/image-300x77.png 300w, https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2023\/08\/image-768x198.png 768w, https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2023\/08\/image-1536x397.png 1536w, https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2023\/08\/image.png 1751w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>The 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\n<p>Paste the above SQL statements into SQLFlow and we got:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" loading=\"lazy\" width=\"1024\" height=\"418\" src=\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2023\/08\/\u5fae\u4fe1\u622a\u56fe_20230803221928-1024x418.png\" alt=\"\" class=\"wp-image-2925\" srcset=\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2023\/08\/\u5fae\u4fe1\u622a\u56fe_20230803221928-1024x418.png 1024w, https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2023\/08\/\u5fae\u4fe1\u622a\u56fe_20230803221928-300x123.png 300w, https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2023\/08\/\u5fae\u4fe1\u622a\u56fe_20230803221928-768x314.png 768w, https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2023\/08\/\u5fae\u4fe1\u622a\u56fe_20230803221928.png 1358w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p><a href=\"https:\/\/sqlflow.gudusoft.com\/#\/\" target=\"_blank\" rel=\"noreferrer noopener\">Gudu SQLFlow<\/a> is widely used in the data governance. As tool of the Industry leading companies, it\u00a0provides\u00a0SQL-related data lineage analysis features. You can try it yourself on: https:\/\/sqlflow.gudusoft.com<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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: As an automated data lineage tool, SQLFlow is also able to analyze Stored Procedure and generates relative data lineage. Let&#8217;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 lineage\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":"<!-- This site is optimized with the Yoast SEO plugin v19.4 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Data lineage for Stored Procedure in SQLFlow<\/title>\n<meta name=\"description\" content=\"Data lineage for Stored Procedure in SQLFlow\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dpriver.com\/blog\/2023\/08\/data-lineage-for-stored-procedure-in-sqlflow\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Data lineage for Stored Procedure in SQLFlow\" \/>\n<meta property=\"og:description\" content=\"Data lineage for Stored Procedure in SQLFlow\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dpriver.com\/blog\/2023\/08\/data-lineage-for-stored-procedure-in-sqlflow\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and Data Blog\" \/>\n<meta property=\"article:published_time\" content=\"2023-08-03T14:24:27+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-08-09T13:22:31+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2023\/08\/image-1024x264.png\" \/>\n<meta name=\"author\" content=\"leo gu\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"leo gu\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\",\"name\":\"SQL and Data Blog\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"sameAs\":[],\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"contentUrl\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"width\":251,\"height\":72,\"caption\":\"SQL and Data Blog\"},\"image\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"name\":\"SQL and Data Blog\",\"description\":\"SQL related blog for database professional\",\"publisher\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/2023\/08\/data-lineage-for-stored-procedure-in-sqlflow\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/2023\/08\/data-lineage-for-stored-procedure-in-sqlflow\/\",\"name\":\"Data lineage for Stored Procedure in SQLFlow\",\"isPartOf\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\"},\"datePublished\":\"2023-08-03T14:24:27+00:00\",\"dateModified\":\"2023-08-09T13:22:31+00:00\",\"description\":\"Data lineage for Stored Procedure in SQLFlow\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/2023\/08\/data-lineage-for-stored-procedure-in-sqlflow\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dpriver.com\/blog\/2023\/08\/data-lineage-for-stored-procedure-in-sqlflow\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/2023\/08\/data-lineage-for-stored-procedure-in-sqlflow\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.dpriver.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Data lineage for Stored Procedure in SQLFlow\"}]},{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/2023\/08\/data-lineage-for-stored-procedure-in-sqlflow\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/2023\/08\/data-lineage-for-stored-procedure-in-sqlflow\/\"},\"author\":{\"name\":\"leo gu\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/9e80d249b6da4e572bd8590b0789bc14\"},\"headline\":\"Data lineage for Stored Procedure in SQLFlow\",\"datePublished\":\"2023-08-03T14:24:27+00:00\",\"dateModified\":\"2023-08-09T13:22:31+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/2023\/08\/data-lineage-for-stored-procedure-in-sqlflow\/\"},\"wordCount\":172,\"publisher\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/9e80d249b6da4e572bd8590b0789bc14\",\"name\":\"leo gu\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/24976e2e4ca7dd476652bb26bd09392b?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/24976e2e4ca7dd476652bb26bd09392b?s=96&d=mm&r=g\",\"caption\":\"leo gu\"},\"url\":\"https:\/\/www.dpriver.com\/blog\/author\/guyuanhao\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Data lineage for Stored Procedure in SQLFlow","description":"Data lineage for Stored Procedure in SQLFlow","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dpriver.com\/blog\/2023\/08\/data-lineage-for-stored-procedure-in-sqlflow\/","og_locale":"en_US","og_type":"article","og_title":"Data lineage for Stored Procedure in SQLFlow","og_description":"Data lineage for Stored Procedure in SQLFlow","og_url":"https:\/\/www.dpriver.com\/blog\/2023\/08\/data-lineage-for-stored-procedure-in-sqlflow\/","og_site_name":"SQL and Data Blog","article_published_time":"2023-08-03T14:24:27+00:00","article_modified_time":"2023-08-09T13:22:31+00:00","og_image":[{"url":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2023\/08\/image-1024x264.png"}],"author":"leo gu","twitter_card":"summary_large_image","twitter_misc":{"Written by":"leo gu","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Organization","@id":"https:\/\/www.dpriver.com\/blog\/#organization","name":"SQL and Data Blog","url":"https:\/\/www.dpriver.com\/blog\/","sameAs":[],"logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","contentUrl":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","width":251,"height":72,"caption":"SQL and Data Blog"},"image":{"@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"WebSite","@id":"https:\/\/www.dpriver.com\/blog\/#website","url":"https:\/\/www.dpriver.com\/blog\/","name":"SQL and Data Blog","description":"SQL related blog for database professional","publisher":{"@id":"https:\/\/www.dpriver.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.dpriver.com\/blog\/2023\/08\/data-lineage-for-stored-procedure-in-sqlflow\/","url":"https:\/\/www.dpriver.com\/blog\/2023\/08\/data-lineage-for-stored-procedure-in-sqlflow\/","name":"Data lineage for Stored Procedure in SQLFlow","isPartOf":{"@id":"https:\/\/www.dpriver.com\/blog\/#website"},"datePublished":"2023-08-03T14:24:27+00:00","dateModified":"2023-08-09T13:22:31+00:00","description":"Data lineage for Stored Procedure in SQLFlow","breadcrumb":{"@id":"https:\/\/www.dpriver.com\/blog\/2023\/08\/data-lineage-for-stored-procedure-in-sqlflow\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dpriver.com\/blog\/2023\/08\/data-lineage-for-stored-procedure-in-sqlflow\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dpriver.com\/blog\/2023\/08\/data-lineage-for-stored-procedure-in-sqlflow\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dpriver.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Data lineage for Stored Procedure in SQLFlow"}]},{"@type":"Article","@id":"https:\/\/www.dpriver.com\/blog\/2023\/08\/data-lineage-for-stored-procedure-in-sqlflow\/#article","isPartOf":{"@id":"https:\/\/www.dpriver.com\/blog\/2023\/08\/data-lineage-for-stored-procedure-in-sqlflow\/"},"author":{"name":"leo gu","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/9e80d249b6da4e572bd8590b0789bc14"},"headline":"Data lineage for Stored Procedure in SQLFlow","datePublished":"2023-08-03T14:24:27+00:00","dateModified":"2023-08-09T13:22:31+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dpriver.com\/blog\/2023\/08\/data-lineage-for-stored-procedure-in-sqlflow\/"},"wordCount":172,"publisher":{"@id":"https:\/\/www.dpriver.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/9e80d249b6da4e572bd8590b0789bc14","name":"leo gu","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/24976e2e4ca7dd476652bb26bd09392b?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/24976e2e4ca7dd476652bb26bd09392b?s=96&d=mm&r=g","caption":"leo gu"},"url":"https:\/\/www.dpriver.com\/blog\/author\/guyuanhao\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts\/2923"}],"collection":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/comments?post=2923"}],"version-history":[{"count":6,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts\/2923\/revisions"}],"predecessor-version":[{"id":2933,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts\/2923\/revisions\/2933"}],"wp:attachment":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/media?parent=2923"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/categories?post=2923"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/tags?post=2923"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}