{"id":681,"date":"2012-02-27T23:54:11","date_gmt":"2012-02-27T12:54:11","guid":{"rendered":"http:\/\/ntsblog.homedev.com.au\/?p=681"},"modified":"2020-08-08T21:34:33","modified_gmt":"2020-08-08T11:34:33","slug":"powershell-return-value-storedprocedure-executenonquery","status":"publish","type":"post","link":"https:\/\/ntsblog.homedev.com.au\/index.php\/2012\/02\/27\/powershell-return-value-storedprocedure-executenonquery\/","title":{"rendered":"PowerShell get &#8220;Return Value&#8221; from StoredProcedure ExecuteNonQuery"},"content":{"rendered":"<div id=\"ntsbl-584820412\" class=\"ntsbl-before-content ntsbl-entity-placement\"><script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-6288941070289539\" crossorigin=\"anonymous\"><\/script><ins class=\"adsbygoogle\" style=\"display:inline-block;width:728px;height:90px;\" \ndata-ad-client=\"ca-pub-6288941070289539\" \ndata-ad-slot=\"9356781486\"><\/ins> \n<script> \n(adsbygoogle = window.adsbygoogle || []).push({}); \n<\/script>\n<\/div><p>The scenario is that from PowerShell you want to do the following:<\/p>\n<p>1. Execute a stored Procedure<br \/>\n2. Check the Return Code from the stored procedure call.<br \/>\n3. Perform conditional logic based upon whether the stored procedure succeeded or failed<\/p>\n<p>The following will show you how to:<\/p>\n<p>1. Create a PowerShell function to execute a stored procedure.<br \/>\n2. Call this stored procedure and extract the return code into an integer variable<br \/>\n3. Apply conditional logic based upon the return code<\/p>\n<p>I struggled with getting this to work and did not find any exact examples that explained it exactly as I wanted.<\/p>\n<h2>Environment Setup<\/h2>\n<ol>\n<li style=\"list-style-type: none;\">\n<ol>\n<li style=\"list-style-type: none;\">\n<ol>\n<li>SQL Server \/ Express &#8211; I am running SQL Sever 2008 R2 Express on the local host<\/li>\n<li>Create an empty Database called &#8220;TestDB&#8221;<\/li>\n<li>PowerShell configured with Remote-Signed Execution Policy. If you don&#8217;t know how to do this go here <a href=\"https:\/\/ntsblog.homedev.com.au\/index.php\/2012\/02\/27\/set-powershell-executionpolicy\/\" target=\"_new\" rel=\"noopener noreferrer\">Set PowerShell Execution Policy<\/a><\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<h2>StoredProcedure<\/h2>\n<p>Create a stored procedure as follows on your TestDB<\/p>\n<pre class=\"\">IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prcTest]') AND type in (N'P', N'PC'))\nDROP PROCEDURE [dbo].[prcTest]\nGO\n\nCreate Procedure prcTest\n\t@value int\nas\nbegin\n\tDeclare @return float\n\n\tBEGIN TRY\n\n\t\t-- could return divide by 0 error if value = 0\n\t\tset @return = 1 \/ @value;\n\n\t\tReturn 1 -- success\n\n\tEND TRY\n\tBEGIN CATCH\n\t\t-- will return 0 when there has been a divide by zero error\n\t\treturn 0\n\tEND CATCH\nend\n<\/pre>\n<p>This procedure will accept a single integer parameter and divide by it. If zero is passed in the procedure will fail with a divide by zero error and return the return code of 0.<br \/>\nAny other number will succeed.<\/p>\n<ul>\n<li>1 = Success; and<\/li>\n<li>0 = Error<\/li>\n<\/ul>\n<p>(NOTE: I specifically reversed the normal codes returned from Stored Procedures because if there is a connection problem and the following powershell script cannot execute it will return 0 which needs to be treated as a failure, so 1 must be the success code)<\/p>\n<h2>PowerShellScript<\/h2>\n<p>Create a file called test.ps1 in your c:\\Scripts directory.<\/p>\n<p>You can run this from the PowerShell ISE or you can create a .bat file and past this line in it:<\/p>\n<pre class=\"\">PowerShell.exe -noexit c:\\scripts\\test.ps1<\/pre>\n<p>The Power Shell Script is as follows:<\/p>\n<pre class=\"\">\nfunction ConnectionString()\n{\n    return \"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestDB;Data Source=.\\SQLExpress\";\n}\n\nfunction executeStoredProcedure($value)\n{\n\n    $connection = ConnectionString;\n    $query = \"prcTest\";\n\n    $sqlConnection = new-object System.Data.SqlClient.SqlConnection $connection\n    $sqlConnection.Open() \n\n    $sqlCmd = new-object System.Data.SqlClient.SqlCommand(\"$query\", $sqlConnection) \n\n    $sqlCmd.CommandType = [System.Data.CommandType]\"StoredProcedure\" \n\n    $sqlCmd.Parameters.AddWithValue(\"@value\", $value)\n\n    $sqlCmd.Parameters.Add(\"@ReturnValue\", [System.Data.SqlDbType]\"Int\")\n    $sqlCmd.Parameters[\"@ReturnValue\"].Direction = [System.Data.ParameterDirection]\"ReturnValue\" \n\n    $sqlCmd.ExecuteNonQuery() | out-null\n    $sqlConnection.Close() \n\n    [int]$sqlCmd.Parameters[\"@ReturnValue\"].Value\n\n}\n\n# Test 1 - Should fail\n#======================================\nWrite-Host \"=====================\"\nWrite-Host \"TEST 1\"\nWrite-Host \"=====================\"\n$out = executeStoredProcedure(0)\nWrite-Host $out\n$returnValue = $out[2]\nWrite-Host $returnValue\n\nif($returnValue -eq $null -or $returnValue -eq 0)\n{\n    Write-Host \"An Error Occured\"\n}\nelse\n{\n    Write-Host \"Success\"\n}\n\n# Test 2 success\n# ---------------\n# removed the Write-Host lines\n#======================================\nWrite-Host \"=====================\"\nWrite-Host \"TEST 2\"\nWrite-Host \"=====================\"\n$out = executeStoredProcedure(1)\n$returnValue = $out[2]\n\nif($returnValue -eq $null -or $returnValue -eq 0)\n{\n    Write-Host \"An Error occurred\"\n}\nelse\n{\n    Write-Host \"Success\"\n}\n<\/pre>\n<p>I will talk you through a few of the interesting points of above.<\/p>\n<p><strong>ConnectionString Fuction<\/strong><br \/>\nI do this so I can have my connection string centralised in my script and that way if<br \/>\nI need to change the SQL Server credentials I am using I only need to make the change in one place<\/p>\n<p><strong>[int]$sqlCmd.Parameters[&#8220;@ReturnValue&#8221;].Value<\/strong><br \/>\nThis gets the &#8220;ReturnValue&#8221; from the stored procedure and then casts it to an integer.<\/p>\n<p>Note: I did try code like;<\/p>\n<pre class=\"\">$rc = [int]$sqlCmd.Parameters[\"@ReturnValue\"].Value\nreturn $rc\n<\/pre>\n<p>But it made no difference to the output of the function.<\/p>\n<p><strong>$returnValue = $out[2]<\/strong><br \/>\nI will confess I don&#8217;t now a great deal about PowerShell but I have worked through this problem to get a working solution.<br \/>\nIf you execute it and look at the output from the &#8220;Write-Host $out&#8221; you will see it appears to be an array type object and it prints as<\/p>\n<pre class=\"\">@value @ReturnValue 1\n<\/pre>\n<p>I figured this is a zero based array so I just accessed the value in position 3 i.e. $out[2], which is already of type &#8220;int&#8221; due to the cast in the function.<\/p>\n<p><strong>Conditional Logic<\/strong><br \/>\nSo now I have the integer return value in a variable $returnValue I can use it in my conditional logic. If the value was null or == 0 then it must be in error else success.<\/p>\n<p>Implementing this means you can execute a stored procedure and if it fails , you can cancel any further processing in the Script.<\/p>\n<p>Nice \ud83d\ude09<\/p>\n<h2>Update &#8211; Oct 2012<\/h2>\n<p>I recently had to use this code again and I found that the best solution is to have the stored procedure return &#8220;non-zero&#8221; values, eg 1 for success, -1 for failure.<br \/>\nThe issue is that the return code from the &#8220;executeStoredProcedure&#8221; call will be zero in certain catastrophic conditions. For example, break your connection string on purpose and the stored procedure will never get executed and the return code from &#8220;executeStoredProcedure&#8221; is zero.<\/p>\n<p>By using non-zero return codes you now have 3 possible states, 1 &#8211; success, -1 failure, 0 &#8211; catastrophic failure.<br \/>\nI extended my code to use &#8220;blat.exe&#8221; to send admin emails on return code zero so that if my powershell script failed to run someone is notified.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The scenario is that from PowerShell you want to do the following: 1. Execute a stored Procedure 2. Check the Return Code from the stored [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[6],"tags":[],"class_list":["post-681","post","type-post","status-publish","format-standard","hentry","category-code"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/681","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/comments?post=681"}],"version-history":[{"count":0,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/681\/revisions"}],"wp:attachment":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/media?parent=681"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/categories?post=681"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/tags?post=681"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}