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

Nasa

I had to set EnableAutoSelect = false. otherwise petapoco kept trying to put a select clause in from of my EXEC – Stubblefield

If you add a ; before the EXEC PetaPoco won't add the SELECT: .Execute(";EXEC InsertCust @C – Industrialist

Adding that semicolon is a little like doing your own SQL injection. I think that db.EnableAutoSelect = false is the cleaner solution. – Thaumatrope

Answer 1

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.

Nasa

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() – Fideicommissum

That's it: using double @ for the store procedure parameters and single @ for petapoco parameters does the trick. Thanks. – Slingshot

Answer 2

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

Thaumatrope

Answer 3

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!

Intervale