Stored Procedures with output parameters using PetaPoco

I’m working on a web application where I’m using the great mirco orm PetaPoco for my data access. Is a really great and super fast ORM that works with SQL Server, SQLCE. MySQL and more.

I’m not gonna cover the basics of PetaPoco in this blog post so if you have not worked with it please read the introduction from TopTen Software.

Now. In this solution we where working with stored procedures (SP) in the SQL Server which is quite simple with PetaPoco, to execute a SP called OutputDemo with two parameters, just write

var sql = Sql.Builder.Append(";EXEC OutputDemo @0, @1, "Markus",29);

​So that's easy. But what if we need to grab some output parametersfrom the sp as well? That took me some more work to figure out. I googled a lot on the subject and there was not really any good recourse so i figured i had to share my solution.

First, let’s look look at the stored procedure that I’ve created for this demo

CREATE PROCEDURE [dbo].[OutputDemo]
    @name varchar(50),
    @age int,
    @resName varchar(50) OUTPUT,
    @resAge int OUTPUT
AS
SET @resAge= @age + 10
SET    @resName = 'Name: ' + @name

A super simple sp that will set the values of the output parameters depending in the value in the input parameters for name and age. So the resAge-output parameter will have the value of the inputted age plus 10, and the resName-output parameter will just be prefixed with “Name:”.

Now. To use this and to get the values back from the query there is couple of thing we need to do. First we need to create variables that holds the returning values from the output parameters.

var opName = new SqlParameter("@resName", SqlDbType.VarChar);
opName .Direction = ParameterDirection.Output;
opName .Size = 50;

var opAge= new SqlParameter("@resAge", SqlDbType.Int);
opAge.Direction = ParameterDirection.Output;
opAge.Size = 20;

This is regular SqlParameters – I’ve just ignored the fact that this would “tie” me to SQL Server since my SP’s are created in SQL Server is just don’t care about this dependency. The important thing to notice is the Direction and the Size-properties that both are mandatory. Next wee need to create the query:

var s = Sql.Builder.Append(";EXEC OutputDemo @0, @1, @2 OUTPUT, @3 OUTPUT",
           "Markus",
           29, 
           opName ,
           opAge
          );

As you see here I’m adding the word “OUTPUT” after each variable for the output parameters – this tells the ADO.NET and PetaPoco that these variables should be populated with the return values of the output parameters.

So with this code executed the value of opName should be “Name: Markus” and opAge should be 39. Here’s the complete code:

var opName = new SqlParameter("@resName", SqlDbType.VarChar);
opName .Direction = ParameterDirection.Output;
opName .Size = 50;
var opAge= new SqlParameter("@resAge", SqlDbType.Int);
opAge.Direction = ParameterDirection.Output;
opAge.Size = 20;
var s = Sql.Builder.Append(";EXEC OutputDemo @0, @1, @2 OUTPUT, @3 OUTPUT",
  "Markus",
  29, 
  opName ,
  opAge
 );
new Database().Execute(s);