SQL Server Brain Basher of the Week #045 - Stored Procedure or Function

A question related to Stored Procedure is very common during an interview and many get stuck with it when it has to be discussed in details. What everyone thinks is, Stored Procedure is a pre-compiled code just like and EXE (Of course it is not) and because of that it can execute the logic added fast. Yes, it is true that you get some performance benefits but it is not the right way to think about it. What if a scenario like below is given and a question is asked.......

Assume that you need to implement a business logic that suppose to do some calculation based on inputs given. The result is always a single integer value and the code of the implementation is very complex. Logic requires few tables to be read and do some complex calculations. Should I use a Stored Procedure for this or a Function?

Generally, If I am the interviewer, I do not expect just an answer saying "Stored Procedure" or "Function". I prefer a lengthy answer explaining why we should go for either Stored Procedure or Function.

Let's see some key points for making the decision. Considering the return value and inputs, it looks like it is better to use a function than a stored procedure. Because the implementation is similar to a scalar function. You need to get more details on the logic that needs to be implemented. If it needs loading data into temporary containers, remember, function does not support temporary tables but table variables. And we need to know how it going to be used. Is it going to be used with SELECT statements? If so, you know that stored procedure cannot be used with SELECT statement, specifically with columns or WHERE clause. As per the requirement, it looks like it does not require to do INSERT, UPDATE or DELETE. Do not forget, these statements are not allowed inside functions.

Not only that, though you do not need to consider with the given scenario, remember, function does not support transactions and TRY/CATCH blocks.

This is what we expect from the interviewee. If interviewee can explain the answer with details, it forced the interviewer to think as He is smart. So be smart!