Not too long ago, I was experimenting with ways to minimize some code duplication in sprocs. Specifically, I wanted to figure out a way to centralize the SELECT X, Y, Z parts of my stored procedures.
For example, I had the following three sprocs:
- GetAllArticles
- GetArticleById
- GetArticlesFromTags
I wrote GetAllArticles first, and it was a basic sproc:
SELECT ArticlelId, Description, DateSubmitted, Status
FROM Articles
Eventually, I wrote GetArticleById:
SELECT ArticlelId, Description, DateSubmitted, Status
FROM Articles
WHERE ArticleId = @ArticleId
Humm...I'd done this a thousand times before...and like always, something didn't seem right. These selects are mapped (straightforwardly and manually) to an Article object. The problem comes if I add/remove a property. I change my 1 class definition and my 1 mapping, but I need to change each and every sproc where I pull an "Article", which might be a lot.
My solution was to let my Stored Procedures get the primary keys, store them (maybe in a temp table?) and call on another sproc to select the actual columns. something like (I'm doing this from the top of my head, since this isn't the actual approach I took):
--GetAllArticles:
DECLARE #temp (id INT)
INSERT INTO #temp
SELECT ArticleId from Articles
EXEC GetAritlcesFromTempTable
DROP TABLE #temp
--GetArticleById:
CREATE TABLE #temp (id INT)
INSERT INTO #temp
SELECT @ArticleId
EXEC GetAritlcesFromTempTable
DROP TABLE #temp
--GetAritlcesFromTempTable
SELECT ArticlelId, Description, DateSubmitted, Status
FROM Articles WHERE ArticleId IN (SELECT id FROM #temp)
Now, truth be told, I was playing with SQL Server 2005's XML capabilities at the time, so I went the XML route instead of the temp table route, but you get the idea. I never did get to play with it much, but I always thought the approach had some value.
updated: with some corrections provided by Jorn (3rd comment)