This post originated from an RSS feed registered with .NET Buzz
by douglas reilly.
Original Post: One more SQL SP Issue..
Feed Title: Doug Reilly's Weblog
Feed URL: http://www.asp.net/err404.htm?aspxerrorpath=/dreilly/rss.aspx
Feed Description: ASP.NET and More...
Stored procedures also will open up a maintenance problem. The reason for this is that they form an API by themselves. Changing an API is not that good, it will break a lot of code in some situations. Adding new functionality or new procedures is the "best" way to extend an existing API. A set of stored procedures is no different. This means that when a table changes, or behaviour of a stored procedure changes and it requires a new parameter, a new stored procedure has to be added.
Not true. In most cases, you can add a parameter with a default value to the existing stored procedure, and that will then allow the new parameter to be used, but will not break existing code that does not need to vary from the default value of that new parameter.
Also, I would use a temp table or table variable over cursors, and in many cases, given a temp table, you can do the type of set based uperations on the temp table that SQL Server was created for. In any event, it will be more efficient than looping through the result set on the client in most cases.