Calling stored procedures with parameters in PetaPoco

Question

I want to be able to call a stored proc with named parameters in PetaPoco.

In order to call a stored proc that does a search/fetch:

Can I do something like this:

return db.Fetch<Customer>("EXEC SP_FindCust",
new SqlParameter("@first_name", fName),
new SqlParameter("@last_name", lName),
new SqlParameter("@dob", dob));

Also, how can I call a stored proc that does an insert?

return db.Execute("EXEC InsertCust @CustID = 1, @CustName = AAA")

Thanks, Nac

I had to set EnableAutoSelect = false. otherwise petapoco kept trying to put a select clause in from of my EXEC — Al W, Nov 06 '12 at 21:51
If you add a `;` before the EXEC PetaPoco won't add the SELECT: `.Execute(";EXEC InsertCust @C` — Morphed, Jan 10 '13 at 15:05
Adding that semicolon is a little like doing your own SQL injection. I think that `db.EnableAutoSelect = false` is the cleaner solution. — asherber, May 19 '17 at 20:31

score 27 · Accepted Answer · edited Mar 27 '18 at 15:37

27

Update:

I tried the following for fetch and insert and it worked perfectly:

var s = PetaPoco.Sql.Builder.Append("EXEC SP_FindCust @@last_name = @0", lname);
s.Append(", @@first_name = @0", fName);
s.Append(", @@last_name = @0", lName);
s.Append(", @@dob = @0", dob);
return db.Query<Cust>(s);

This can be improved further to pass SQL parameters.

edited Mar 27 '18 at 15:37

Gaspa79

5,488
4
40
63

answered Aug 05 '11 at 17:54

Tech Xie

927
4
12
24

Hi there, that's great thanks, but do you know how I can return the identity from the insert? My stored proc is INSERT INTO t_Book VALUES(@BookName, @ParentBookId) SELECT @BookId = SCOPE_IDENTITY() – Dr. Andrew Burnett-Thompson Sep 06 '11 at 21:27
That's it: using double @ for the store procedure parameters and single @ for petapoco parameters does the trick. Thanks. – JCallico Nov 25 '11 at 15:17

score 3 · Answer 2 · answered Nov 24 '18 at 16:09

3

As of v6.0.344-beta, PetaPoco now supports stored procedures without needing to use EXEC. See https://github.com/CollaboratingPlatypus/PetaPoco/wiki/Stored-procedures

answered Nov 24 '18 at 16:09

asherber

2,508
1
15
12

score 2 · Answer 3 · answered Mar 28 '19 at 12:15

In my case, I did the following

db.EnableAutoSelect = false;

return db.Fetch<Customer>(@"EXEC SP_FindCust 
@@first_name = @first_name, 
@@last_name = @last_name, 
@@dob = @dob", new {
  first_name = fName,
  last_name = lName,
  dob = dob
});

It worked!

Calling stored procedures with parameters in PetaPoco

3 Answers3

Linked