{"id":1,"date":"2024-12-13T19:22:10","date_gmt":"2024-12-13T19:22:10","guid":{"rendered":"https:\/\/infotechville.com\/blog\/?p=1"},"modified":"2024-12-17T16:37:51","modified_gmt":"2024-12-17T16:37:51","slug":"procedure-parameter-values","status":"publish","type":"post","link":"https:\/\/infotechville.com\/blog\/index.php\/2024\/12\/13\/procedure-parameter-values\/","title":{"rendered":"Identifying SQL procedure parameter values at execution time"},"content":{"rendered":"\n<p><strong>Problem description:<\/strong><\/p>\n\n\n\n<p>An application (programming code) that uses SQL procedure with parameters. The procedure manipulates data on some procedure\u2019s conditions depending on input parameters. At the same time, there is no option to debug the code to check the input parameters and which conditional part of the procedure will be executed for a problematic case.<\/p>\n\n\n\n<p><strong>Solution:<\/strong><\/p>\n\n\n\n<p>Microsoft SQL Server Management Studio and its tool \u2018SQL Server Profiler\u2019 can help with identifying the procedure\u2019s parameters at the execution time.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p>Go to Tools -> SQL Server Profile in SSMS<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"801\" height=\"260\" src=\"https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/ssms1.png\" alt=\"\" class=\"wp-image-41\" srcset=\"https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/ssms1.png 801w, https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/ssms1-300x97.png 300w, https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/ssms1-768x249.png 768w\" sizes=\"auto, (max-width: 801px) 100vw, 801px\" \/><\/figure>\n\n\n\n<p>Connect to SQL Server (Database)<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"527\" src=\"https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-1024x527.png\" alt=\"\" class=\"wp-image-42\" srcset=\"https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-1024x527.png 1024w, https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-300x154.png 300w, https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-768x395.png 768w, https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image.png 1263w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>In &#8216;Trace Properties&#8217; window, &#8216;General&#8217; tab, update &#8216;Trace name:&#8217;. Or leave as default.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"502\" src=\"https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-2-1024x502.png\" alt=\"\" class=\"wp-image-44\" srcset=\"https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-2-1024x502.png 1024w, https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-2-300x147.png 300w, https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-2-768x376.png 768w, https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-2.png 1031w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>In &#8216;Trace Properties&#8217; window, &#8216;Events Selection&#8217; tab, update filters. Pay attention to Column Filters (Edit Filter), you may need to remove filters if they were defined before.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"891\" height=\"515\" src=\"https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-3.png\" alt=\"\" class=\"wp-image-45\" srcset=\"https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-3.png 891w, https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-3-300x173.png 300w, https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-3-768x444.png 768w\" sizes=\"auto, (max-width: 891px) 100vw, 891px\" \/><\/figure>\n\n\n\n<p>Once ready \u2013 click &#8216;Run&#8217;<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"496\" src=\"https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-4-1024x496.png\" alt=\"\" class=\"wp-image-48\" srcset=\"https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-4-1024x496.png 1024w, https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-4-300x145.png 300w, https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-4-768x372.png 768w, https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-4.png 1027w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>it starts running &#8230;<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1023\" height=\"532\" src=\"https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-5.png\" alt=\"\" class=\"wp-image-49\" srcset=\"https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-5.png 1023w, https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-5-300x156.png 300w, https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-5-768x399.png 768w\" sizes=\"auto, (max-width: 1023px) 100vw, 1023px\" \/><\/figure>\n\n\n\n<p>Go to the application that uses the procedure and perform some actions where the issue happens (which starts the sql procedure). Then Pause the Profiler.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1025\" height=\"552\" src=\"https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-6.png\" alt=\"\" class=\"wp-image-50\" srcset=\"https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-6.png 1025w, https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-6-300x162.png 300w, https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-6-768x414.png 768w\" sizes=\"auto, (max-width: 1025px) 100vw, 1025px\" \/><\/figure>\n\n\n\n<p>In our case there is a SQL Procedure below. It has 19 parameters and we are not sure what are values at execution time.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"933\" height=\"638\" src=\"https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-7.png\" alt=\"\" class=\"wp-image-51\" srcset=\"https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-7.png 933w, https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-7-300x205.png 300w, https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-7-768x525.png 768w\" sizes=\"auto, (max-width: 933px) 100vw, 933px\" \/><\/figure>\n\n\n\n<p>Let&#8217;s go to SQL Server Profile (!! the top of the profiler window, click on the first row in the window) and find our procedure (just CTRL + F)<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"493\" src=\"https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-8-1024x493.png\" alt=\"\" class=\"wp-image-52\" srcset=\"https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-8-1024x493.png 1024w, https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-8-300x144.png 300w, https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-8-768x370.png 768w, https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-8.png 1030w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>result<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"501\" src=\"https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-9-1024x501.png\" alt=\"\" class=\"wp-image-53\" srcset=\"https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-9-1024x501.png 1024w, https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-9-300x147.png 300w, https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-9-768x376.png 768w, https:\/\/infotechville.com\/blog\/wp-content\/uploads\/2024\/12\/image-9.png 1036w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p>This approach helps me much when I&#8217;m working with legacy code\/environment and there are no debug options.<\/p>\n\n\n\n<p>Cheers.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Problem description: An application (programming code) that uses SQL procedure with parameters. The procedure manipulates data on some procedure\u2019s conditions depending on input parameters. At&#8230;<\/p>\n<div class=\"more-link-wrapper\"><a class=\"more-link\" href=\"https:\/\/infotechville.com\/blog\/index.php\/2024\/12\/13\/procedure-parameter-values\/\">Read more<span class=\"screen-reader-text\">Identifying SQL procedure parameter values at execution time<\/span><\/a><\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-1","post","type-post","status-publish","format-standard","hentry","category-sql-server","entry"],"_links":{"self":[{"href":"https:\/\/infotechville.com\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/infotechville.com\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/infotechville.com\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/infotechville.com\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/infotechville.com\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=1"}],"version-history":[{"count":6,"href":"https:\/\/infotechville.com\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1\/revisions"}],"predecessor-version":[{"id":54,"href":"https:\/\/infotechville.com\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1\/revisions\/54"}],"wp:attachment":[{"href":"https:\/\/infotechville.com\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=1"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/infotechville.com\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=1"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/infotechville.com\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=1"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}