There is no "one answer" to this: either way the execution is pretty much the same, because an SP is just a sequence of instructions that is executed in the same way that it would be if you entered them as a string and sent them over as an SqlCommand. The SP will be precompiled is all, which might save a small (and I do mean small) amount of processing time at the SQL server end. but not much!
It's a balance between clarity and structure: some people like to put even trivial SELECT or UPDATE statements into an SP, but I feel that makes code harder to read as you can't tell what an SP is doing without consulting SSMS and looking - "inline" code for simple stuff is a lot easier to maintain.
Generally, I use an SP where it's complex: where it's too easy to make a mistake, or you don't need to know exactly how it works, just get the results you wanted. For example:
SELECT a.Name, b.EpCount
FROM Serials a
JOIN
(SELECT SerialId, Count(SerialNo) AS EpCount
FROM Episode GROUP BY SerialId) AS b
ON a.Id = b.SerialId
is an SP called CountEpisodes because it returns each series of a TV show together with the number of episodes - it's something I use in a few places, and it's a little complex to work out. So get it right once, create an SP and it becomes trivial next time.
Submit an article or tip