Stored procedures should expose detailed contracts - by Greg Low - Australia

Status : 

 


69
0
Sign in
to vote
ID 525653 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 1/18/2010 11:26:30 PM
Access Restriction Public

Description

Hi folks, stored procedures should expose detailed contracts. I've detailed the argument here: 
http://sqlblog.com/blogs/greg_low/archive/2010/01/19/stored-procedures-time-for-a-real-contract.aspx
Sign in to post a comment.
Posted by Robert Heinig III on 10/9/2014 at 3:02 AM
And I have IF 0=1 SELECT constructs in all my sp's designed for consumption by SSIS or SSRS for years. If both product components need metadata from the primary product component - and pull dirty tricks to get it - a clear indicator that something is amiss.
Posted by Mark Freeman on 5/3/2013 at 7:48 AM
If I recall correctly, Oracle has had this for many, many years.
Posted by SAinCA on 3/4/2013 at 1:32 PM
Could we add, "Vital for SSRS report consumption of SPs"?

The first non-variable SELECT dictates the fields seen by the Query Builder in SSDT/BIDS.

A data-contract will definitively state the expected returned data so we don't have to code dummy SELECT statements and comment-uncomment just to provoke SSDT/BIDS into recognizing the correct results. If one inherits SPs that use SELECT as a means of debugging based on a parameter, the interim debug code gets mistaken by SSDT/BIDS as the resultset. Despite sending a non-Debug value to the SP to run the query, SSDT/BIDS has already decided upon the results it will see - hence the fake SELECT and commenting.

A hearty +1 for this one.
Posted by bige1030 on 8/12/2012 at 7:50 PM
It's a good idea, but here are my reservations:
1. Specifying a contract should be optional, for backward compatibility with existing stored procedures.
2. Again, for backward compatibility (in both the application and existing SPs), we should still be able to use @@ROWCOUNT and/or return an empty result set to deal with "no results" without having to throw an exception.
3. This would make T-SQL stored procedures even more nonstandard than they already are. If there are any plans to make standard SQL part of T-SQL, this feature should be implemented either as the standard says (if it's part of the standard at the time of implementation) or in a syntax that could be proposed for the next SQL standard.
Posted by GregGalloway on 6/6/2012 at 1:28 PM
I agree that something akin to WITH RESULT SETS should be syntax in the proc declaration, not syntax used by the caller of the sproc.
Posted by Microsoft on 1/28/2010 at 1:47 PM
Thanks for bringing up this request. We will consider this for the next major release.
Posted by rajmundr on 1/23/2010 at 6:49 PM
This is good guys, but I've already utilize such concept of contracts with my SPs since SQL 2005. This is in form of registering an XML schema instance with SQL server and then assign the result set into a so called typed XML variable. This works like a charm for both input and output. That kicks back immediately any invalid input data without even executing a SProc code. And yes, I no more communicate with the db issuing any T-SQL commands nor receiving typical rowsets in a traditional manner (IMHO it's archaic). It's bad since does not allow hierarchical datasets and results in chatty style of communication. Brrr... people still do it... multiple output result sets from a given SP. Why?

All my contracts are the XSD in SQL Server. It's very easy to pull what XSDs (with full definition) are associated with particular SP using DMVs. Great for generating documentation on the fly. I can understand any system written in this style in minutes. Also, I do not have my system bloated with 4 CRUD procedures per every table. I have a fully business (domain) centric design. One SP per business transaction, some tables even do not allow delete to anyone thus no deletion SP exists for those. SP may itself insert into 20 tables, application does not need to know any of that logic and it simplifies the app design to a magnitude of order less. Many people call this a business logic and tend to put it outside in an application code with help of ORM, but they might not be aware about difference between persitence logic versus business logic. My SP more or less contain persistence logic following a rule "Perform this closer where the data are". Also, SQL guarantees static compilation errors if you try code against something which does not exists in the schema, so no worries. People who are afraid of writing SQL and SP calling it ancient language wants the ORM while it really does not help. Any change to db breaks your app and many times it's not as simple as renaming few columns when the association tables gets added or columns got removed. People do not understand the SPs are not primarily for performance, but for encapsuation of db design. Who does not get it I say I treat my db as a service repository like Amazon or Twitter. Do we care how it stores the data? We just call their APIs. All my SPs are service methods associated with contracts. If someone still does not get it and asks why treat db as web services (per my statement "application should be dummy and know knothing about a db (design)") I do refer to an example of a SOA design. Think of why SOA has been invented? To abstract internals of the other system or layer. To make design and consuming simpler.

While your idea is great, I'm not sure if it's worth for MS to enhance it the way you desire causing others to just write more and more code within SP declaration (and maintenance of changes). Again, the contract problem is solved for at least me already and it's so beautiful to communicate with the db sending pure objects only (serialize and deserialize in a app).

It's a great abstraction over a physical db design and structural changes. It's XXI century and I really believe it's time for most people to switch to rich datasets exchanged in form of strogly typed and validated XML. To validate outgoing result I compose my result set as an XML and assign it to a typed variable (tied to a schema) which instantly validates the result is according to the contract. That gives even more business centric validation of transaction performed as expected. If my SP is supposed to create two offsetting transactions (use case: reversal) I can indicate this in a schema that "Summary/PaymentRecord" node must appear exactly twice in it and possibly the second amount must be negative, so if for any reason one insert would not be successfull or the other developer forgot to write it that would not be let gone by SQL. This is an added value instead of just checking for @@ROWCOUNT after inserting into any table and still does not solve the problem of new developer not knowing when he altered something he should insert one more row with negative amount. I simply can return/record a XML summary of a business transaction and that summary also gets validated. If anything would change that would be some form of a higher level guard. This is very agile while I do not like this buzzword personally. Anyway doing it XML schema way you have an added value of documented expected result or summary at the end expressed in form of tangible XSD contract. It introduces less bugs by any new developer possibly unfamiliar with a specific nature of your business.

I understand maybe if you return a million of records really it may nor be suitable (not everything is for everybody), then at least this my technique may be used in variation, use XML as an input and return regular rowset. It all depends on one needs, but perfectly worked in my enterprise system and believe me for any new enhancement or fix request I rarely need to go and mess around the application code. Boosted my productivity to the max since I test only one layer (db), because the contract guarantees the app works with this particular contract already. I can quickly apply the fixes and structure enhancements to my db without touching an app. Everything gets abstracted.

Some people may be concerned about performance, do not ask me, try for yourself and see if that works for you. Just as an example ~300 payment batch posting (moving from one table to the other) done ORM way went down from 1-2 minutes down to under 1 second using all logic in SP. But what about db/app developer performance? The fixing or enhancement maintenance time in my team could got down even to a couple of hours instead of weeks to recompile an app, retest whole thing, push app code through the staging server and wait for next app build and then publish cycle and then pray the result set matches to what the app expects. It's horrible. For those who want to continue that way good luck. I'm just fixing my SP or tables, test these and voilaaa!
Posted by Sandeep Koniki on 1/20/2010 at 6:31 PM
Excellent idea - I've been complaining about this since SOA came out! It would be nice to think each Tier can be independantly managing without breaking stuff or knowing the internal details of sps. As long as the contract is followed, people can just call the sp!
Posted by Greg Low - Australia on 1/19/2010 at 2:37 PM
Hey Jamie, great point on the return values. I've done an update here with my thoughts on it: http://sqlblog.com/blogs/greg_low/archive/2010/01/20/stored-procedure-contracts-return-values.aspx
Posted by AaronBertrand on 1/19/2010 at 6:41 AM
Jamie, why would you need something other than integers for return types? Isn't this what output parameters are for? You shouldn't be using return values to return *data*, IMHO.
Posted by Jamie Thomson on 1/19/2010 at 3:22 AM
One other thought....if we were to impose contracts on the sproc resultset(s) why shouldn't the same be true of the return value?
Could we have sproc return types that are something other than integers?
Could we have sproc return types that are constrained either by DDL or entries in a table?

Just a thought!
Posted by Jamie Thomson on 1/19/2010 at 1:03 AM
I agree, this is absolutely required. The SSIS dataflow is heavily reliant upon metadata from data sources and stored procedures do not provide any such metadata.