Here's an argument for SPs that I haven't heard. Flamers, be gentle with the down tick,

Since there is overhead associated with each trip to the database server, I would suggest that a POSSIBLE reason for placing your SQL in SPs over embedded code is that you are more insulated to change without taking a performance hit.

For example. Let's say you need to perform Query A that returns a scalar integer.

Then, later, the requirements change and you decide that it the results of the scalar is > x that then, and only then, you need to perform another query. If you performed the first query in a SP, you could easily check the result of the first query and conditionally execute the 2nd SQL in the same SP.

How would you do this efficiently in embedded SQL w/o perform a separate query or an unnecessary query?

Here's an example:

--This SP may return 1 or two queries.

SELECT @CustCount = COUNT(*) FROM CUSTOMER

IF @CustCount > 10 SELECT * FROM PRODUCT

Can this/what is the best way to do this in embedded SQL?