Execute stored procedure with PetaPoco

Question

I have a stored procedure which returns back a table value.

Here is my stored procedure:

PROCEDURE [GetPermitPendingApproval] 
    @permitYear int = NULL, 
AS
BEGIN
        SELECT [p].[ID]
          ,[p].[PermitNumber] 
          ,[p].[PermitTypeID]
          ,[p].[ApplicationDate]
          ,[u].[FirstName]
          ,[u].[MI]
          ,[u].[LastName]
          ,[u].[Suffix]
          ,[u].[ProfessionalTitle]
          ,[u].[WorksFor] 
      FROM [SciCollUser] u 
            INNER JOIN UserPermit up ON up.[UserID] = u.[ID] 
            INNER JOIN Permit p ON p.[ID] = [up].[PermitID] 
     WHERE (@permitYear IS NULL OR p.PermitYear = @permitYear) 
    ORDER BY [p].[ApplicationDate] ASC;
END

I am not sure whether we have such a way to use PetaPoco to execute a stored procedure and get a returned data as a table? Please help!

As normally I can execute a stored procedure with the follow script but it is not the way I want.

db.Execute("EXEC GetPermitPendingApproval @permitYear=2013");

score 32 · Answer 1 · answered Feb 12 '14 at 13:02

32

You need to put a semicolon before EXEC.

var result = db.Fetch<dynamic>(";EXEC GetPermitPendingApproval @@permitYear = @0", 2013);

answered Feb 12 '14 at 13:02

anssi

729
7
9

can you explain why the semi colon is needed? Is this due to some internal implementation of something within PetaPoco? – rdans May 18 '15 at 13:19
2

@rdans Yes it is a work around ";" allows you to end the 1st statement to start another. In other words, this ignores the 1st statement inserted by petapoco making it unused. – Bamboo Jul 10 '15 at 08:45
this horrible workaround still works like a charm in 2023, thanks – Gaspa79 Jun 01 '23 at 14:04

score 18 · Answer 2 · answered Apr 13 '16 at 19:55

Answer is probably late, but I hope, that it will be useful for future generations. You should turn EnableAutoSelect option to false on PetaPoco database object db.EnableAutoSelect = false; Otherwise it will keep adding SELECT NULL FROM [Object] to you sql statement.

It's good, that it's possible to debug PetaPoco sources.I've found this option only because of debugging!

Eduardo Molteni · Answer 3 · 2018-04-16T14:24:00.767

9

You get List<T> where T is a POCO type with the properties you want to map or a Dynamic

So the actual syntax is:

var result = db.Fetch<dynamic>(";EXEC GetPermitPendingApproval @0", 2013);

or

var result = db.Fetch<dynamic>(";EXEC GetPermitPendingApproval @permitYear",
                                                       new {permitYear = 2013});

edited Apr 16 '18 at 14:24

answered Nov 21 '13 at 19:59

Eduardo Molteni

38,786
23
141
206

2

It does not work and here is an error: Invalid object name '[Object]' from this query string "SELECT NULL FROM [Object] EXEC GetPermitPendingApproval" – Sam Jan 29 '14 at 06:34

score 5 · Answer 4 · answered Nov 24 '18 at 16:07

5

As of v6.0.344-beta, PetaPoco now supports stored procs natively, so you can do:

var result = db.FetchProc<MyClass>("GetPermitPendingApproval", new { permitYear = 2013 });

answered Nov 24 '18 at 16:07

asherber

2,508
1
15
12

Execute stored procedure with PetaPoco

4 Answers4