Doug Reilly's Weblog

Embedded Reporting of the Information Age...

One more SQL SP Issue..

Frans says:

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.

Posted: Nov 18 2003, 10:28 AM by douglas.reilly | with 2 comment(s)
Filed under:

Comments

Frans Bouma said:

A cursor creates a temptable.

That default parameter is a good one, preserving the logic will work with 'a' default value, which is not always the case.
# November 18, 2003 11:15 AM

Douglas Reilly said:

True, but using the default parameter will very often prevent lots of existing code that does not need the newly added feature from breaking. If this logic is handled in dynamic SQL, I expect things will be more complicated (embed a parameter in a SELECT statement, and it will need to be filled in with something).

The alternative that I use (in the case of a breaking change) is to create a spWhatever2 that accepts the additional parameter required. That way, other code (perhaps a Web or Windows app that does not need to use the parameter) can continue using the old "contract", and the new code can be either new logic or it can just manipulate the new parameter and then call the old spWhatever procedure internally.
# November 18, 2003 11:21 AM
Leave a Comment

(required) 

(required) 

(optional)

(required)