When to use stored procedure and when function in SQL Server 2008?

There’s no technically right answer here – I think I’d do the SP calling SP.

Yikes! that sounds like a mess! The truth is that whatever you do is going to be rough but since you already have all that SP code… I’d probably just keep it and try to make it work by calling the other SP. May not be the perfect way to do it but if you’ve got to bang it out that’s the way to go. You can always try to perfect it later with a function… in fact you should try to optimize later if there is time… my $.02

@stevenelliottjr , It doesn’t that hard to copy the code into function and call that function from every SP, but is that the right way to do it?

It’s a question of the future code maintenance efficiency, how not to have a duplicate code in SQL and be able to update code only once.

If the function gets called for every row, just bring it into the procedure as part of your SQL. SQL Server will be able to optimize it.

The trick in SQL Server (especially for us procedural programmers) is to learn to think in sets instead of processes. It’s not always easy to do.

Hi @Grisson , my function is returning a TABLE. The function get one parameter and by that choosing many rows from the table, so I get back a TABLE. I’m not doing anything to a one row, so maybe you are right, this should be a small stored procedure and I just need to call it fron another stored procedure when I need to.

@Ranimi23 I’m not sure you gain anything by switching from a function to a stored procedure. If you are only calling the function or inner procedure once from the outer procedure, then it shouldn’t make much difference.