Calling stored procedures with parameters in PetaPoco

sql petapoco
Tech Xie picture Tech Xie · Aug 5, 2011 · Viewed 16.2k times · Source

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

Answer

Tech Xie picture Tech Xie · Aug 5, 2011

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.

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More