Getting a Return value from a Stored Procedure in PetaPoco

20. February 2012 Howto 1

For the past several months I’ve been using PetaPoco for almost all of my data access needs in .Net, and I’ve absolutely loved it. Until today.

I had a stored procedure from a legacy database that returns a code (e.g. -1, invalid user id, 0 success etc.)  I sat down to execute this stored procedure and ran the following command:

https://gist.github.com/1870197

However, PetaPoco kept throwing an exception.  As I  stepped into the code, I found out why, the cmd.ExecuteScalar was always returning null.  I tried a bunch of different solutions, and none of them worked.  Then, I opened up SSMS (which should have been my first step) and started looking at how it executes stored procedures.  That’s when it hit me.  PetaPoco is passing a command with a type ‘text’ so I needed to specify more data to make it work.  The query below did the trick.

https://gist.github.com/1870209

Pay particular notice to the @@result.  If you simply put ‘@result’ PetaPoco will expect a parameter named result.  Putting the dual ‘@’ tells PetaPoco that this is a SQL parameter.


1 thought on “Getting a Return value from a Stored Procedure in PetaPoco”

  • 1
    a on June 15, 2015 Reply

    HI,

    IN
    ” .Append(“exec GetData @userId, @date”,
    new {userId = userId, @date = DateTime.Now})”

    You are missing :
    ” .Append(“exec @@result = GetData @userId, @date”,
    new {userId = userId, @date = DateTime.Now})”

Leave a Reply

Your email address will not be published. Required fields are marked *