Stored Procedures?

Why Stored Procedures?

After 3 monthes of writing Stored Procedues (SP) to my last enterprise project, I can't say that they are that good. I'm glad that I have learned to apply this SQLserver programming language, anyway. For before this, people told me that I have to get more experienced, when I tried to discuss the pros and the cons; now I am not yet an regular expert, but I guess I have seen enough to evaluate it in comparision to other approaches.

• Performance: Executing stored procedures are exactly as fast as executing Views or regular Queries
• Caching: Regular queries have the same advantage in caching as SP
  (the server has to calculate the best way of execution only once for a sequence of same queries)
• network-traffic: In some scenarios there is indeed a strong advantage in using SP.
  e.g. Transforming data received by queeries from another server in cascading steps without tranferrring data throughout the network more than necessary - this may be an enormous performance boost in some settings.
• But: Performance gains often (I said often, I did not say always) are payed by CPU-time-consumption of the SQL-server!
  Why dragging the entire performance of the server down for some complicated calculations, when it has to process simultaneously other client-queries? Why not use another machine to do that as separate process and why not use the machine of the client?!
  - I imagine that some experts might say: the prescript is to take more CPU and and more RAM - but why buy when there is enough around waiting for the server machine?

Sorry, folks, this I could never understand before, nor do I understand it now.
Eventually, the design of the Stored Procedure-Language (I mean T-SQl of MS-SQLServer) is as primitive as the first BASIC idioms. For example, you have to save intermediate results physically on the server, because there is not a thing like an Array. I've tried to write a CSV-file on the server (funny idea, you might say, but i had to do it for some strange demand of people that need the CSV-way anyhow) - and this takes a hundred times more time than doing it on a client PC with streaming technics.
You have to write your sourcecode in a text-editor-window whithout autocompletion, syntax-highlighting and other IDE-sugar.

But why do the smart professional developer use this language at all?

- I don't know!
May be, it is easier to write loops and do it step by step than doing it by pure brain breaking SQL. But this you could do with much more ease and convenience at your personal computer in the IDE of your choice (Visual Studio (LINQ!) / Eclipse).

May be experts are often using that language while doing server-maintainance-jobs, migratings, upgradings etc. Most of scripts to make such batch-processes are written in TSQL. So they might use this language for all purposes on SQlServer, simply because there are used to it.