handling stored procedures and functions

Question

Hi guys, I like database design and I'm currently developing one in SQL Server 2008, one thing I dislike is making all the simple procedures like 'person_exist' or 'get_person_data'. How do you handle functions and procedures involving database data? am I doing right?

Answer 1

Personally, I would tend to write queries for "simple" data directly in SQL, instead of creating separate procedures for them. If a query is as simple as SELECT * FROM person, why bother going through all the boilerplate of creating a formal DB procedure for it? Plus, many ORM frameworks can generate these simple queries directly anyway. I would reserve procedures/functions for more complex queries that rightfully belong in the database layer.

Of course that is just my opinion, and I have seen it done both ways. You could also argue that you should create a procedure, function, or view for every database query, so that changes made on the database side can be completely isolated from client applications. There is some merit to that approach, but it will be more work to create all of these 'simple' procedures.

Answer 2

based on the OP's comment:

the thing is that this database will be used by other developers, they won't know the database itself...

Think set based when writing your procedures!

Watch out for creating your procedures in a way that will lead to needing loops to use them. If the application needs to call the same procedure N times in a loop to get the data then the performance will be much lower than if the application could make a single call and get a complete single result set of data. Think, one procedure call to get the header data and one call to get all the children details (not a loop of calls for each child).

This is a common problem I see many people fall into when designing "API" type stored procedures.