0

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

I would like to know about people's thoughts and experience on it. Also, would like to know when to use the views. I am not looking for the definition of these db objects. A practical scenario discussion would be good

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1254579
  • 3,901
  • 21
  • 65
  • 104

2 Answers2

1

In my eyes it is a very bad habit to use SPs just to read data.

You must distinguish between

  • SP: A batch, normally multi statement, your are allowed to do (almost) everything. The biggest flaw is, that you cannot continue with the SPs result easily and that - if you want to use a SPs return in further queries - you always have to write this into a correctly declared table (real, temp or variable). This can be a lot of error prone typing. And furthermore, the optimizer will not be able to deal with this performantly.

  • TV-UDF (Table valued User Defined Function): One must be aware of the fact that there exist two flavours: single-statement (ad-hoc) or multi-statment. The first is good, the second (in almost all cases) very bad! One advantage over the VIEW is, that parameters and their handling is pre-compiled.

  • VIEW: This is as good as an ad-hoc TV-UDF. You can declare it with schema binding and deal with it almost as if it was a table (indexes and so on)...

Fazit: Use SPs for UPDATE, DELETE, any kind of data or structure manipulation but not for sole reading.

Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • Why is it a very bad habit to use SPs to read data? – zedfoxus Dec 15 '15 at 21:17
  • As I've written in my answer, the biggest flaw is the usage of the resultset **after** the execution. The return of a TV-UDF can be used as simple as `SELECT * FROM dbo.MyFunction(@prm1,@prm2)` or can be used directly in a JOIN or APPLY. This will **never** work with a SP. Secondly an inlined call is always better in performance. There are **very** rare situations (e.g. where you have to use dynamic SQL), when a SP is good to read data. – Shnugo Dec 15 '15 at 21:21
  • Understood. I feel differently about this. When multiple applications (C#, Java, etc.) are making calls to retrieve information and process information locally, I have appreciated retrieving data with a common SP instead of applications writing parameterized SQL statements. Complex cursor-based business logic in SPs have also helped me. So my feeling is that CRUD through SPs has its place; UDFs, as you mentioned quite well, also has its place. – zedfoxus Dec 15 '15 at 21:27
  • @zedfoxus Cursor are **very** bad too and should be avoided whereever possible. They are - in almost all cases - not needed. Read about "row based" and "set based" thinking. And: There is not reason to query with "parameterized SQL statements". A pre-compiled TV-UDF is as handsome as a SP. – Shnugo Dec 15 '15 at 21:30
  • It's cool that we have differing opinions – zedfoxus Dec 15 '15 at 21:32
0

In my daily work for example, I use sp's to retrieve, store, update and delete records, I execute those sps from web application most of the time, but sometimes when some sp needs some complex or repetitive operation then I use a function to reuse that operation if needed or to leave a more clean code in my sp.

We do not normally use Views, but you can use them for simplicity, you don't need to make the same long Select command with joins if you just call the view.

that's my experience, sry for my english :).

Audor
  • 46
  • 5