{"id":668,"date":"2009-09-11T00:00:00","date_gmt":"2009-09-11T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/calling-stored-procedures-from-net-applications\/"},"modified":"2021-05-17T18:36:40","modified_gmt":"2021-05-17T18:36:40","slug":"calling-stored-procedures-from-net-applications","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/development\/dotnet-development\/calling-stored-procedures-from-net-applications\/","title":{"rendered":"Calling Stored Procedures from .NET Applications"},"content":{"rendered":"<p>In this article, I&#8217;m going to illustrate how a stored procedure can be called from a .NET process, including all the ways in which data can be passed to the database. I&#8217;ll use PowerShell in the example code just because so many examples exist already in the other .NET languages, but I&#8217;ll add a version of the finished routine in IronPython and VB.NET.<\/p>\n<div id=\"pretty\">\n<p>When you\u00a0 access a database application from a client, the chances are that you will need to call stored procedures \u00a0in the database layer. You&#8217;ll probably use the SQLClient\u00a0 if you are using .NET, or SQL Native Client in unmanaged code. From Linux, you&#8217;d use JDBC.\u00a0 In any event, all the hard work is done for you.<\/p>\n<p>in .NET, there are three very similar ways of accessing SQL Server. If you want to access other databases such as MySQL, Access or SQLite, then you&#8217;ll use the net library <code>System.Data.ODBC <\/code>but we are going to use <code>System.Data.SQLClient,<\/code> as it supports all the current SQL Server datatypes.The third main approach is <code>System.Data.OLEDB<\/code> but but this is minority-interest.<\/p>\n<p>Stored Procedures provide more alternatives in the way they can pass data back to the application. You can pass data and information<\/p>\n<ul>\n<li>As one or more results<\/li>\n<li>As a series of messages (e.g. DBCC)<\/li>\n<li>As an integer return code<\/li>\n<li>In output or input\/output parameters.<\/li>\n<li>In an error<\/li>\n<\/ul>\n<p>Because of this, the interface can be slightly more complicated than just using Ad-Hoc SQL or views, and getting back single results. The payback is that Stored Procedures tend to work faster, are much more secure, are more economical with server memory, and can contain a lot more logic. Additionally, it makes teamwork easier: As long as the name of the stored procedure, what it does, and the parameters remain the same, it also allows someone else in the team to work on the database code without you having to change your client software.<\/p>\n<p>Most Stored procedures are very simple, and are easy to access.\u00a0 Stored procedures can, if so wished, return absolutely nothing, and such things are pretty simple to implement, but are not particularly common in actual use. Even if you are making a call to a database to record an event or update\u00a0 a record, you are likely to want to know if it was successful or not, so already, you are likely to be tackling at least one transfer medium in the list I&#8217;ve given.<\/p>\n<p>Let&#8217;s start off with a very simple application log consisting of a table and a stored procedure. We&#8217;ll refine it later. To try this out, just create a trial database in SSMS and use this code (I&#8217;m using SQL Server 2005) by pasting it into a query window and executing it&#8230;<\/p>\n<pre class=\"lang:c# theme:vs2012\">--delete the procedure if it exists\r\nIF EXISTS ( SELECT\u00a0 *\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 information_schema.Tables\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 TABLE_Name LIKE 'ActivityLog' ) \r\n\u00a0\u00a0\u00a0 DROP TABLE [dbo].[ActivityLog]\r\ngo\r\n\u00a0\r\nCREATE TABLE [dbo].[ActivityLog]\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0 [ActivityLog_id] [int] IDENTITY(1, 1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0 [Type] [int] NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0 [creator] [varchar](80) NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0 [insertiondate] [datetime] NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0 [LogString] [varchar](2000) NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0 [terminationdate] [datetime] NULL,\r\n\u00a0\u00a0\u00a0\u00a0 CONSTRAINT [PK_dbo_ActivityLog] PRIMARY KEY CLUSTERED ([ActivityLog_id] ASC)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WITH (PAD_INDEX=OFF, STATISTICS_NORECOMPUTE=OFF, IGNORE_DUP_KEY=OFF,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON) ON [PRIMARY]\r\n\u00a0\u00a0\u00a0 )\r\nON\u00a0 [PRIMARY]\r\n\u00a0\r\nEXEC sys.sp_addextendedproperty @name=N'MS_Description',\r\n\u00a0\u00a0\u00a0 @value=N'primary key for the table', @level0type=N'SCHEMA',\r\n\u00a0\u00a0\u00a0 @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ActivityLog',\r\n\u00a0\u00a0\u00a0 @level2type=N'COLUMN', @level2name=N'ActivityLog_id'\r\nEXEC sys.sp_addextendedproperty @name=N'MS_Description',\r\n\u00a0\u00a0\u00a0 @value=N'Date the record was created (automatic)', @level0type=N'SCHEMA',\r\n\u00a0\u00a0\u00a0 @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ActivityLog',\r\n\u00a0\u00a0\u00a0 @level2type=N'COLUMN', @level2name=N'insertiondate'\r\nEXEC sys.sp_addextendedproperty @name=N'MS_Description',\r\n\u00a0\u00a0\u00a0 @value=N'Date for the termination of the record', @level0type=N'SCHEMA',\r\n\u00a0\u00a0\u00a0 @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ActivityLog',\r\n\u00a0\u00a0\u00a0 @level2type=N'COLUMN', @level2name=N'terminationdate'\r\nEXEC sys.sp_addextendedproperty @name=N'MS_Description',\r\n\u00a0\u00a0\u00a0 @value=N'This table is the audit log of all activity in the application',\r\n\u00a0\u00a0\u00a0 @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',\r\n\u00a0\u00a0\u00a0 @level1name=N'ActivityLog'\r\nGO\r\n\u00a0\r\nALTER TABLE [dbo].[ActivityLog]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ADD CONSTRAINT [DF_ActivityLog_Type] DEFAULT ((1)) FOR [Type]\r\nALTER TABLE [dbo].[ActivityLog]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ADD CONSTRAINT [DF_ActivityLog_creator] DEFAULT (user_name()) FOR [creator]\r\nALTER TABLE [dbo].[ActivityLog]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ADD CONSTRAINT [DF_ActivityLog_insertiondate] DEFAULT (getdate()) FOR [insertiondate]\r\nALTER TABLE [dbo].[ActivityLog]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ADD CONSTRAINT [DF_ActivityLog_LogString] DEFAULT ('') FOR [LogString]\r\nGO\r\n\u00a0\r\n--delete the procedure if it exists\r\nIF EXISTS ( SELECT\u00a0 *\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 information_schema.routines\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 Routine_Name LIKE 'InsertLogString'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND Routine_Type LIKE 'Procedure' ) \r\n\u00a0\u00a0\u00a0 DROP PROCEDURE InsertLogString\r\ngo\r\n\u00a0\u00a0\u00a0 \r\nCREATE PROCEDURE InsertLogString\r\n\/*\r\nLogs an event in a activity log table and adds the \r\ndate, user and so on\r\n\u00a0\r\n\u00a0usage: \r\nInsertLogString 4, 'Just written a stored procedure'\r\n*\/\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0 @Type INT,--the type of entry\r\n\u00a0\u00a0\u00a0\u00a0 @LogString VARCHAR(2000)--the actual text\r\n\u00a0\u00a0\u00a0 )\r\nAS \r\n\u00a0\u00a0\u00a0 INSERT\u00a0 INTO ActivityLog (Type, LogString)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 @Type,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --the type of entry\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @LogString\u00a0\u00a0\u00a0 --the actual text\r\n\u00a0go\r\n\u00a0\r\n<\/pre>\n<p>Now we can easily call this logging stored procedure \u00a0from our client application just by executing the stored procedure. For some reason, the hardest part always seems to be the configuration string. It is worth getting this right before you start, maybe using an office application to test it. There are even websites that exist just to give sample Configuration strings, but they are mostly for ODBC. SQLClient is pretty easy.<\/p>\n<p>\u00a0Once you have got the configuration string right, it is downhill all the way. Here is a simple PowerShell example. (Remember\u00a0 to alter the connection string in the code to suit your server and database, of course.)<\/p>\n<pre class=\"lang:c# theme:vs2012\">$ErrorActionPreference = \"Stop\"\r\n#none of our errors are recoverable\r\n$conn = new-Object System.Data.SqlClient.SqlConnection(\"Server=MyServer;DataBase=MyDatabase;password=MyPassword; uid=MyUserID\")\r\n$conn.Open() | out-null\r\n$cmd = new-Object System.Data.SqlClient.SqlCommand(\"InsertLogString\", $conn)\r\n$cmd.CommandType = [System.Data.CommandType]::StoredProcedure\r\n#\r\n$cmd.Parameters.Add(\"@Type\",[system.data.SqlDbType]::Int) | out-Null\r\n$cmd.Parameters['@type'].Direction = [system.data.ParameterDirection]::Input\r\n$cmd.Parameters['@type'].value=2\r\n$cmd.Parameters.Add(\"@LogString\",[system.data.SqlDbType]::VarChar) | out-Null\r\n$cmd.Parameters['@LogString'].Direction = [system.data.ParameterDirection]::Input\r\n$cmd.Parameters['@LogString'].value = 'This has happened quite suddenly'\r\n$cmd.ExecuteNonQuery() #because we are not getting a result back\r\n$conn.Close()\r\n<\/pre>\n<p>So we have called a stored procedure with a couple of parameters. Hmm. We&#8217;re not quite finished.\u00a0 That connection string shouldn&#8217;t really be in code, as you&#8217;d have to change it if your credentials change. You can store connection strings in XML configuration files and read them in with the<code> ConfigurationManager<\/code> class.\u00a0 More importantly, we need to be able to see any errors that happen. There are a number of ways of doing this. In PowerShell, though, we&#8217;re rather restricted. We&#8217;ll take an easy option and adapt the code slightly where the $cmd.Executenonquery() is called<\/p>\n<pre class=\"lang:c# theme:vs2012\">$ErrorActionPreference = \"SilentlyContinue\" \r\n$rdr = $cmd.Executenonquery()\u00a0 #because we are not getting a result back\r\n\u00a0if(-not $?) {$error[0]|format-list -force}\r\n$conn.Close()\r\n<\/pre>\n<p>This provides us with more information than we really need, but it keeps the code simple. Next, we worry about validating the input. There is no point in firing an error on invalid input as the application will want to know what type of\u00a0 problem there was, and react accordingly. A lot of stored procedures send back a return code. We can always read that.<\/p>\n<p>We&#8217;ll first make a slight change to the stored procedure so it returns 0 if it all went well, 1 if the log type was out of range, 2 if it failed to write to the log and 3 if something else happened&#8230;.<\/p>\n<pre class=\"lang:c# theme:vs2012\">--delete the procedure if it exists\r\nIF EXISTS ( SELECT\u00a0 *\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 information_schema.routines\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 Routine_Name LIKE 'InsertLogString'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND Routine_Type LIKE 'Procedure' ) \r\n\u00a0\u00a0\u00a0 DROP PROCEDURE InsertLogString\r\ngo\r\n\u00a0\u00a0\u00a0 \r\nCREATE PROCEDURE InsertLogString\r\n\/*\r\nLogs an event in a activity log table and adds the \r\ndate, user and so on\r\n\u00a0\r\n\u00a0usage: \r\nInsertLogString 4, 'Just written a stored procedure'\r\n\u00a0\r\nDeclare @ret int\r\nExecute @ret=InsertLogString 4, 'Just added a return code'\r\nSelect @Ret\r\n*\/\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0 @Type INT,--the type of entry\r\n\u00a0\u00a0\u00a0\u00a0 @LogString VARCHAR(2000)--the actual text\r\n\u00a0\u00a0\u00a0 )\r\nAS \r\nDeclare @Rowcount int\r\n\u00a0\u00a0\u00a0 IF @Type NOT BETWEEN 1 AND 12 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RETURN 1 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\r\n\u00a0\u00a0\u00a0 INSERT\u00a0 INTO ActivityLog (Type, LogString)\r\n\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SELECT\u00a0 @Type,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --the type of entry\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @LogString\u00a0\u00a0\u00a0\u00a0\u00a0 --the actual text\r\nset @rowcount=@@Rowcount\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0 IF @rowcount=0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RETURN 2\r\n\u00a0\u00a0\u00a0 IF @rowcount=1 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RETURN 0\r\n\u00a0\r\n\u00a0\u00a0\u00a0 RETURN 3\r\n\u00a0go\r\n\u00a0\r\n<\/pre>\n<p>Now, at the end of the client routine, we have access to this return code. In this example, we&#8217;ll just print it out but you&#8217;d want to react in the procedure according to the value of the return code. Apologies for not doing a complete worked example but we want to keep things simple just so we can illustrate the basics..<\/p>\n<pre class=\"lang:c# theme:vs2012\">$cmd.Parameters.Add(\"@rtn\",[system.data.SqlDbType]::Int) | out-Null\r\n$cmd.Parameters['@rtn'].Direction = [system.data.ParameterDirection]::ReturnValue\r\n$ErrorActionPreference = \"SilentlyContinue\" \r\n$rdr = $cmd.Executenonquery()\u00a0 #because we are not getting a result back\r\n\u00a0if(-not $?) {$error[0]|format-list -force}\r\n$conn.Close()\r\nwrite-output $cmd.Parameters['@rtn'].value\r\n\u00a0\r\n<\/pre>\n<p>Okay, but what about getting something back from this routine? Let&#8217;s pretend that it would be useful to return the date on the database\u00a0 at which the log entry was made.<\/p>\n<p>Well, we have two choices. We can make the @LogString into an InputOutput variable or we can create an output variable just to hold the Date and time. Let&#8217;s do the latter, since using a variable for two purposes is a potential snare..<\/p>\n<pre class=\"lang:c# theme:vs2012\">--delete the procedure if it exists\r\nIF EXISTS ( SELECT\u00a0 *\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 information_schema.routines\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 Routine_Name LIKE 'InsertLogString'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND Routine_Type LIKE 'Procedure' ) \r\n\u00a0\u00a0\u00a0 DROP PROCEDURE InsertLogString\r\ngo\r\n\u00a0\u00a0\u00a0 \r\nCREATE PROCEDURE InsertLogString\r\n\/*\r\nLogs an event in a activity log table and adds the \r\ndate, user and so on\r\n\u00a0\r\n\u00a0usage: \r\nInsertLogString 4, 'Just written a stored procedure'\r\n\u00a0\r\nDeclare @ret int\r\nDeclare @DateOfLog datetime\r\nExecute @ret=InsertLogString 4, 'Just added a return code',@DateOfLog output\r\nSelect @Ret,@DateOfLog\r\n*\/\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0 @Type INT,--the type of entry\r\n\u00a0\u00a0\u00a0\u00a0 @LogString VARCHAR(2000),--the actual text\r\n\u00a0\u00a0\u00a0\u00a0 @DateOfEntry DATETIME OUTPUT--the date of the insertion\r\n\u00a0\u00a0\u00a0 )\r\nAS \r\n\u00a0\u00a0\u00a0 DECLARE @Rowcount INT\r\n\u00a0\u00a0\u00a0 IF @Type NOT BETWEEN 1 AND 12 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RETURN 1 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\r\n\u00a0\u00a0\u00a0 INSERT\u00a0 INTO ActivityLog (Type, LogString)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 @Type,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --the type of entry\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @LogString\u00a0\u00a0\u00a0\u00a0\u00a0 --the actual text\r\n\u00a0\u00a0\u00a0 SET @rowcount=@@Rowcount\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0 SET @DateOfEntry=GETDATE()\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0 IF @rowcount=0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RETURN 2\r\n\u00a0\u00a0\u00a0 IF @rowcount=1 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RETURN 0\r\n\u00a0\r\n\u00a0\u00a0\u00a0 RETURN 3\r\n\u00a0go\r\n<\/pre>\n<p>All we then had to do in order to get the value back was to add the following parameter<\/p>\n<pre class=\"lang:c# theme:vs2012\">$cmd.Parameters.Add(\"@DateOfEntry\",[system.data.SqlDbType]::DateTime) | out-Null\r\n$cmd.Parameters[\"@DateOfEntry\"].Direction = [system.data.ParameterDirection]::Output\r\n<\/pre>\n<p>So, we&#8217;ve coped with input parameters, output parameters and return codes. Next we have to deal with a stored procedure that has one or more results.<\/p>\n<p>Just to make life a bit more difficult for ourselves, we&#8217;ll pass back two results. We&#8217;ll return the values in the inserted row, and we&#8217;ll return the time that elapsed between log entries for the last ten log entries (we have the bones of a performance logging system here!).<\/p>\n<p>This time, the stored procedure is beginning to bristle with outputs. It also is getting a lot nearer the sort of stored procedures I use. A stored procedure that returns a customer object that may have a number of contact numbers, addresses, notes, and purchases, and all this information is easier to transfer as a series of results rather than a Godzilla view, and the basic customer details such as the surrogate ID can easily be passed in an output variable.<\/p>\n<pre class=\"lang:c# theme:vs2012\">--delete the table if it exists\r\nIF EXISTS ( SELECT\u00a0 *\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 information_schema.Tables\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 TABLE_Name LIKE 'ActivityLog' ) \r\n\u00a0\u00a0\u00a0 DROP TABLE [dbo].[ActivityLog]\r\ngo\r\n\u00a0\r\nCREATE TABLE [dbo].[ActivityLog]\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0 [ActivityLog_id] [int] IDENTITY(1, 1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0 [Type] [int] NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0 [creator] [varchar](80) NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0 [insertiondate] [datetime] NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0 [LogString] [varchar](2000) NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0 [terminationdate] [datetime] NULL,\r\n\u00a0\u00a0\u00a0\u00a0 CONSTRAINT [PK_dbo_ActivityLog] PRIMARY KEY CLUSTERED ([ActivityLog_id] ASC)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WITH (PAD_INDEX=OFF, STATISTICS_NORECOMPUTE=OFF, IGNORE_DUP_KEY=OFF,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON) ON [PRIMARY]\r\n\u00a0\u00a0\u00a0 )\r\nON\u00a0 [PRIMARY]\r\n\u00a0\r\nEXEC sys.sp_addextendedproperty @name=N'MS_Description',\r\n\u00a0\u00a0\u00a0 @value=N'primary key for the table', @level0type=N'SCHEMA',\r\n\u00a0\u00a0\u00a0 @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ActivityLog',\r\n\u00a0\u00a0\u00a0 @level2type=N'COLUMN', @level2name=N'ActivityLog_id'\r\nEXEC sys.sp_addextendedproperty @name=N'MS_Description',\r\n\u00a0\u00a0\u00a0 @value=N'Date the record was created (automatic)', @level0type=N'SCHEMA',\r\n\u00a0\u00a0\u00a0 @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ActivityLog',\r\n\u00a0\u00a0\u00a0 @level2type=N'COLUMN', @level2name=N'insertiondate'\r\nEXEC sys.sp_addextendedproperty @name=N'MS_Description',\r\n\u00a0\u00a0\u00a0 @value=N'Date for the termination of the record', @level0type=N'SCHEMA',\r\n\u00a0\u00a0\u00a0 @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ActivityLog',\r\n\u00a0\u00a0\u00a0 @level2type=N'COLUMN', @level2name=N'terminationdate'\r\nEXEC sys.sp_addextendedproperty @name=N'MS_Description',\r\n\u00a0\u00a0\u00a0 @value=N'This table is the audit log of all transaction activity in the database',\r\n\u00a0\u00a0\u00a0 @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',\r\n\u00a0\u00a0\u00a0 @level1name=N'ActivityLog'\r\nGO\r\n\u00a0\r\nALTER TABLE [dbo].[ActivityLog]\r\nADD CONSTRAINT [DF_ActivityLog_Type] DEFAULT ((1)) FOR [Type]\r\nALTER TABLE [dbo].[ActivityLog]\r\nADD CONSTRAINT [DF_ActivityLog_creator] DEFAULT (USER_NAME()) FOR [creator]\r\nALTER TABLE [dbo].[ActivityLog]\r\nADD CONSTRAINT [DF_ActivityLog_insertiondate] DEFAULT (GETDATE()) FOR [insertiondate]\r\nALTER TABLE [dbo].[ActivityLog]\r\nADD CONSTRAINT [DF_ActivityLog_LogString] DEFAULT ('') FOR [LogString]\r\nGO\r\n\u00a0\r\n--delete the procedure if it exists\r\nIF EXISTS ( SELECT\u00a0 *\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 information_schema.routines\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 Routine_Name LIKE 'InsertLogString'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND Routine_Type LIKE 'Procedure' ) \r\n\u00a0\u00a0\u00a0 DROP PROCEDURE InsertLogString\r\ngo\r\n\u00a0\u00a0\u00a0 \r\nCREATE PROCEDURE InsertLogString\r\n\/*\r\nLogs an event in a activity log table and adds the \r\ndate, user and so on\r\n\u00a0\r\n\u00a0usage: \r\nDeclare @DateOfLog datetime\r\nExecute InsertLogString 4, 'Just written a rather good stored procedure',@DateOfLog output\r\n\u00a0\r\nDeclare @ret int\r\nDeclare @DateOfLog datetime\r\nExecute @ret=InsertLogString 4, 'Just added a return code',@DateOfLog output\r\nSelect @Ret,@DateOfLog\r\n*\/\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0 @Type INT,--the type of entry\r\n\u00a0\u00a0\u00a0\u00a0 @LogString VARCHAR(2000),--the actual text\r\n\u00a0\u00a0\u00a0\u00a0 @DateOfEntry DATETIME OUTPUT--the date of the insertion\r\n\u00a0\u00a0\u00a0 )\r\nAS \r\n\u00a0\u00a0\u00a0 DECLARE @InsertionCount INT,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @InsertedRow INT\r\n\u00a0\u00a0\u00a0 IF @Type NOT BETWEEN 1 AND 12 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RETURN 1 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0 PRINT 'storing '''+@Logstring+''' at '+convert(char(17), GetDate(), 113) \r\n\u00a0\u00a0\u00a0 INSERT\u00a0 INTO ActivityLog (Type, LogString)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 @Type,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --the type of entry\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @LogString\u00a0\u00a0\u00a0\u00a0\u00a0 --the actual text\r\n\u00a0\u00a0\u00a0 SET @InsertionCount=@@RowCount\u00a0 \r\n\u00a0\u00a0\u00a0 SET @insertedRow=@@identity \r\n\u00a0\u00a0\u00a0 SET @DateOfEntry=GETDATE()\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0 SELECT\u00a0 ActivityLog_id,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Type],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 creator,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 insertiondate,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LogString terminationdate\r\n\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 ActivityLog\r\n\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 ActivityLog_ID=@InsertedRow\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --and now the elapsed time between log entiries\r\n\u00a0\u00a0\u00a0 SELECT top 20\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 insertionDate,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [lapsedTime]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = DATEDIFF(ms, InsertionDate,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COALESCE((SELECT TOP 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [next].insertionDate\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0\u00a0\u00a0 ActivityLog AS [next]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0\u00a0\u00a0 [next].ActivityLog_ID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &gt;[current].ActivityLog_ID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER BY\u00a0 [next].ActivityLog_ID ASC\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ), GETDATE())),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LogString\r\n\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 activityLog AS [current]\r\n\u00a0\u00a0\u00a0 order by InsertionDate Desc\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0 IF @InsertionCount=0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RETURN 2\r\n\u00a0\u00a0\u00a0 IF @InsertionCount=1 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RETURN 0\r\n\u00a0\u00a0\u00a0 RETURN 3\r\n\u00a0go<\/pre>\n<p>So all we have to do now is to add the extra logic in the PowerShell script to get and display the two results. Here is the script.<\/p>\n<pre class=\"lang:c# theme:vs2012\">$ErrorActionPreference = \"Stop\"\r\n#none of our errors are recoverable\r\n$conn = new-Object System.Data.SqlClient.SqlConnection(\"Server=MyServer;DataBase=MyDatabase;password=MyPassword; uid=MyUserID\")\r\n$conn.Open() | out-null #open the connection\r\n$cmd = new-Object System.Data.SqlClient.SqlCommand(\"InsertLogString\", $conn)\r\n$cmd.CommandType = [System.Data.CommandType]::StoredProcedure\r\n#now we have created the command and set it to be a stored procedure\r\n#we now add the parameters to the stored procedures\r\n#The @Type parameter\r\n$cmd.Parameters.Add(\"@Type\",[system.data.SqlDbType]::Int) | out-Null\r\n$cmd.Parameters['@type'].Direction = [system.data.ParameterDirection]::Input\r\n$cmd.Parameters['@type'].value=2\r\n#The @LogString parameter\r\n$cmd.Parameters.Add(\"@LogString\",[system.data.SqlDbType]::VarChar) | out-Null\r\n$cmd.Parameters['@LogString'].Direction = [system.data.ParameterDirection]::Input\r\n$cmd.Parameters['@LogString'].value = 'We have just called a stored procedure'\r\n#The @DateOfEntry Output parameter\r\n$cmd.Parameters.Add(\"@DateOfEntry\",[system.data.SqlDbType]::DateTime) | out-Null\r\n$cmd.Parameters[\"@DateOfEntry\"].Direction = [system.data.ParameterDirection]::Output\r\n#and The return code\r\n$cmd.Parameters.Add(\"@rtn\",[system.data.SqlDbType]::Int) | out-Null\r\n$cmd.Parameters['@rtn'].Direction = [system.data.ParameterDirection]::ReturnValue\r\n$ErrorActionPreference = \"SilentlyContinue\" \r\n$rdr = $cmd.ExecuteReader()\u00a0 #because we are getting a result back\r\n\u00a0if(-not $?) {$error[0]|format-list -force}#if we have an error here report it and quit\r\n\u00a0#Remember that you can't read the output variables until you've read all the results!\r\n\u00a0#first result\r\n$ErrorActionPreference = \"Stop\"\r\n$Counter = $rdr.FieldCount\r\nwhile ($rdr.Read()) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 for ($i = 0; $i -lt $Counter; $i++) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @{ $rdr.GetName($i) = $rdr[$i]; }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n}\r\n$rdr.NextResult()#get the next result\r\n$Counter = $rdr.FieldCount\r\n#and display it.\r\nwhile ($rdr.Read()) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 for ($i = 0; $i -lt $Counter; $i++) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @{ $rdr.GetName($i) = $rdr[$i]; }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n}\r\n\u00a0\r\n$conn.Close()\r\n#and we just show we've read the output variables!\r\nwrite-output $cmd.Parameters['@rtn'].value\r\nwrite-output $cmd.Parameters['@DateOfEntry'].value\r\n<\/pre>\n<p>The only task I haven&#8217;t shown is how to get the messages from the stored procedure. If you do PRINT statements, or you want to see other status messages (Normally you switch off the rowcount message using SET NOCOUNT\u00a0 ON)\u00a0 then you have to get hold of these messages. This is done by creating a<b> SqlInfoMessageEventHandler <\/b>delegate, identifying the method that handles the event, \u00a0to listen for the <b>InfoMessage<\/b> event on the <b>SqlConnection<\/b> class. Message-context information such as severity and state are passed as arguments to the callback, because from the system perspective, these messages are just like errors. At its simplist, you might do this&#8230;<\/p>\n<pre>static void ffs(object sender, SqlInfoMessageEventArgs messages)\r\n\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0Console.WriteLine(\"msg-&gt;\" + messages.Message);\r\n\u00a0\u00a0\u00a0 }\r\n<\/pre>\n<p>But this cannot be done with PowerShell v1.<\/p>\n<p>The routine translates easily into IronPython, but error-handling in Python (IronPython)\u00a0is even more arcane than PowerShell, so I&#8217;ve omitted it.<\/p>\n<pre class=\"lang:c# theme:vs2012\">import clr\r\nclr.AddReference('System.Data')\r\nfrom System.Data import *\r\n\u00a0\r\nconn = SqlClient.SqlConnection(\"Server=MyServer;DataBase=MyDatabase;password=MyPassword; uid=MyUserID\")\r\nconn.Open()\r\ncmd = SqlClient.SqlCommand(\"InsertLogString\", conn)\r\ncmd.CommandType = CommandType.StoredProcedure\r\ncmd.Parameters.Add(\"@Type\",SqlDbType.Int)\r\ncmd.Parameters['@type'].Direction = ParameterDirection.Input\r\ncmd.Parameters['@type'].Value=2\r\ncmd.Parameters.Add(\"@LogString\",SqlDbType.VarChar) \r\ncmd.Parameters['@LogString'].Direction = ParameterDirection.Input\r\ncmd.Parameters['@LogString'].Value = 'We have just called a stored procedure'\r\n#The @DateOfEntry Output parameter\r\ncmd.Parameters.Add(\"@DateOfEntry\",SqlDbType.DateTime) \r\ncmd.Parameters[\"@DateOfEntry\"].Direction = ParameterDirection.Output\r\n#and The return code\r\ncmd.Parameters.Add(\"@rtn\",SqlDbType.Int) \r\ncmd.Parameters['@rtn'].Direction = ParameterDirection.ReturnValue\r\nrdr = cmd.ExecuteReader()\u00a0 #because we are getting a result back\r\nCounter = rdr.FieldCount\r\nwhile rdr.Read(): \r\n\u00a0\u00a0\u00a0 for i in range(Counter):\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 print rdr.GetName(i), rdr.GetValue(i)\r\nrdr.NextResult()#get the next result\r\nCounter = rdr.FieldCount\r\n#and display it.\r\nwhile rdr.Read():\r\n\u00a0\u00a0\u00a0 for i in range(Counter):\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 print rdr.GetName(i), rdr.GetValue(i)\r\nrdr.Close()\r\nconn.Close()\r\n#and we just show we've read the output variables!\r\nprint cmd.Parameters['@rtn'].Value\r\nprint cmd.Parameters['@DateOfEntry'].Value\r\n\u00a0\r\n\u00a0<\/pre>\n<p>We have not quite achieved all we want to yet as it would be nice to receive all those PRINT messages from SQL Server. To do this, you would do best to use either VB.NET or C#. The code just requires an event handler. This is explained very well here on MSDN- <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/a0hee08w%28VS.71%29.aspx\">Working with Connection Events<\/a>. Here is the complete code in VB<\/p>\n<pre class=\"lang:c# theme:vs2012\">Imports System\r\nImports system.configuration\r\nImports System.Data\r\nImports System.Data.SqlClient\r\nModule storedprocedure\r\n\u00a0\u00a0\u00a0 Dim process As String\r\n\u00a0\u00a0\u00a0 Dim Messages As String\r\n\u00a0\u00a0\u00a0 Private Sub OnInfoMessage(ByVal sender As Object, ByVal args As System.Data.SqlClient.SqlInfoMessageEventArgs)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim err As System.Data.SqlClient.SqlError\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 For Each err In args.Errors\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Messages += err.Message + vbCrLf\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Next\r\n\u00a0\u00a0\u00a0 End Sub\r\n\u00a0\r\n\u00a0\u00a0\u00a0 Sub Main()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim i As Integer\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim connectionString As String\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim counter As Integer\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Messages = \"\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 connectionString = \"\"Server=MyServer;DataBase=MyDatabase;password=MyPassword; uid=MyUserID\"\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Try\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Using conn As New SqlConnection(connectionString)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 conn.Open()\u00a0 'Open the connection\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AddHandler conn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Using cmd As New SqlCommand(\"InsertLogString\", conn)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 With cmd\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .CommandType = CommandType.StoredProcedure\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'now we have created the command and set it to be a stored procedure\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'we now add the parameters to the stored procedures\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'The @Type parameter\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .Parameters.Add(dd(\"@Type\", SqlDbType.Int)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .Parameters(\"@type\").Direction = ParameterDirection.Input\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .Parameters(\"@type\").Value = 2\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'The @LogString parameter\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .Parameters.Add(\"@LogString\", SqlDbType.VarChar)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .Parameters(\"@LogString\").Direction = ParameterDirection.Input\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .Parameters(\"@LogString\").Value = \"We have just called a stored procedure\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'The @DateOfEntry Output parameter\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .Parameters.Add(dd(\"@DateOfEntry\", SqlDbType.DateTime)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .Parameters(\"@DateOfEntry\").Direction = ParameterDirection.Output\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'and The return code\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .Parameters.Add(\"@rtn\", SqlDbType.Int)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .Parameters(\"@rtn\").Direction = ParameterDirection.ReturnValue\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim rdr As SqlDataReader = .ExecuteReader()\u00a0 'because we are getting a result back\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Remember that you can't read the output variables until you've read all the results!\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'first result\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 counter = rdr.FieldCount\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 While rdr.Read()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 For i = 0 To counter - 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 System.Console.Write(rdr.GetName(i) &amp; \" = \" &amp; rdr.GetValue(i) &amp; vbCrLf)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Next i\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End While\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rdr.NextResult() 'get the next result\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 counter = rdr.FieldCount\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'and display it.\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0While rdr.Read()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 For i = 0 To counter - 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 System.Console.Write(rdr.GetName(i) &amp; \" = \" &amp; rdr.GetValue(i) &amp; vbCrLf)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Next i\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End While\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rdr.Close()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'and we just show we've read the output variables!\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 System.Console.Write(\"@rtn=\" &amp; .Parameters(\"@rtn\").Value() &amp; vbCrLf)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 System.Console.Write(\"@DateOfEntry=\" &amp; .Parameters(\"@DateOfEntry\").Value() &amp; vbCrLf)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End With\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End Using\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 conn.Close()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End Using 'the connection\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Catch ex As Exception\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 System.Console.Write(ex.Message &amp; \" whilst \" &amp; process &amp; vbCrLf)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End Try\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 System.Console.Write(\"Messages=\" &amp; Messages)\r\n\u00a0\u00a0\u00a0 End Sub\r\n\u00a0\r\nEnd Module\r\n\r\n<\/pre>\n<p>So you will see from this article that there are a number of different types of information we need to be able to get from a stored procedure. Some aspects are easy, and appear in demos all around the internet. Other information, such as messages, are fiendish and very language-dependent. Once you feel confident with output variables, return codes, and multiple results, you&#8217;ll find uses for them to make interacting with a database a lot easier.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Everybody knows how to call stored procedures from a .NET application. Right? But then, how often do you see stored procedures used to their full advantage in database applications? William Brewer goes through some of the basics, but uses PowerShell and IronPython as well as VB.NET as the example .NET applications just to freshen the subject up a bit, and avoids all mention of Northwind or AdventureWorks!&hellip;<\/p>\n","protected":false},"author":213195,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143538],"tags":[4143,4229,4242,4178,4168,4635],"coauthors":[6796],"class_list":["post-668","post","type-post","status-publish","format-standard","hentry","category-dotnet-development","tag-net","tag-net-framework","tag-basics","tag-bi","tag-database","tag-powershell"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/668","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/213195"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=668"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/668\/revisions"}],"predecessor-version":[{"id":75019,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/668\/revisions\/75019"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=668"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=668"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=668"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=668"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}