Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored Procedure Development Cycle


Stored Procedure Development Cycle

Author
Message
Robert W Marda
Robert W Marda
SSC Eights!
SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)

Group: General Forum Members
Points: 984 Visits: 82
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/storedproceduredevelopmentcycle.asp



Robert W. Marda
SQL Programmer
Ipreo

ckempste
ckempste
SSC Eights!
SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)

Group: General Forum Members
Points: 885 Visits: 1
Hi Robert

Good article. This and other "change management" process/procedures are critical for ongoing successful change, when I say successful, that mean change with rollout/rollback/hotfix which all cost resources (aka money) that is vert difficult to plan for (let alone truely cost for a business).

I follow a similar approach:
a) develop on the DEV servers, assign base security roles
b) script, rollout to TEST (access restricted purely to the role)
c) formal user testing on TEST
d) run TEST scripts against PREPROD
e) micro test on preprod
f) backup prod db
g) rollout to prod
h) minor functional testing

Simple enough, but few sites follow it.

Cheers

Ck



Chris Kempster
www.chriskempster.com
Author of "SQL Server 2k for the Oracle DBA"


Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3684 Visits: 3118
Yep, agree with all this but I would add documentation to the list.

The situation I am in is where the stored procedures are effectively an interface to the database layer. My developers access the database via that interface layer.

The result of this is that the stored procedures have to be fully documented so that we don't end up with hundreds of stored procedures, all doing something very similar but not quite the same.

As we work with a number of web and cms (content management systems) these applications get hit by a stress testing tool. OK I've never had problems with the database server side of things partly because we tend to over-specify from day one and partly because IIS is the main bottle-neck!

I haven't been able to find any instructions for the Visual Studio Application Performance Explorer but I believe that this would help stress test the database.

We do use SourceSafe but isn't it about time MS updated it? It looks very Windows 3.0.

LinkedIn Profile

Newbie on www.simple-talk.com
Philip Kelley
Philip Kelley
Say Hey Kid
Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)

Group: General Forum Members
Points: 681 Visits: 232
Just to mention, it is possible to set up fairly automated integration between SQL Server and Visual Source Safe using Visual Interdev. (And VSS 6 may be old, but in my experience it's one the most reliable applications Microsoft ever churned out, and when it comes to source code I'll take reliability over appearance any day.)

Setting it up can range from very hard to a royal nightmare [MS made us our own bug fix once], but once properly configured continuous operation is painless and seemless.

It requires doing your development in Visual Interdev, or at least checking code in and out with it. You theoretically can run and debug your code in VI, but given the pain required to set it up in the first place I've never tried to configure that. I tend to write the code in VI (it has a slightly better text editor than Query Analyzer), and then cut and paste and perform testing within QA.


Documentation and duplication of effort is one of our biggest problems, as we have 10+ procedure developers spread across two disparate geographical locations. I would love to see how David.Poole manages this!

Philip



Robert W Marda
Robert W Marda
SSC Eights!
SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)

Group: General Forum Members
Points: 984 Visits: 82
I'm glad to know I am not the only one who does development the way I explained in my article.

David.Poole, you have a good point. I failed to mention documentation. However, I will see if I can create a good article about documentation in stored procedures.

Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.



Robert W. Marda
SQL Programmer
Ipreo

croptop
croptop
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 1
Robert,

I, too, found this to be a great "Best Practices" article on SP development. Allow me to add my voice to those who would be interested in reading your take on the documentation side of things.

John



Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7231 Visits: 2679
.Net has the application test center that is pretty worthwhile for testing. Just record a web session, play it back, get lots of charts and graphs. Supports multiple users per test. Honestly takes 5 minutes to get up and running for a simple scenario.

Andy
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Philip Kelley
Philip Kelley
Say Hey Kid
Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)

Group: General Forum Members
Points: 681 Visits: 232
My concern over documentation is not so much about "internal" documentation as "external". There must be better terms for this, but I can't think of them offhand, so:

"Internal" documentation are the /*comments*/ you put in the code to make it easier for others (including yourself, months later) to figure out what the code is doing. This code (obviously) sits in the stored procedure itself, and so is only accessible to those who have access to the procedure, and only if they open and look at the file.

"External" documentation, as I'm thinking of it, is more a description of what the procedure does and how to use it (inputs and outputs), and not how it actually works. This should be a single object (document? database? file?), readily accessible and understandable by everyone who needs this information, and this could range from the old hands who know the system inside-out to the summer interns or new hires. This is the document that tells you "hey, someone already wrote something that does what you need".

I have no real idea how to create, maintain, and distribute such a document (and again, it wouldn't necessarily be a piece of paper or even a simple text file). But if we had had something like that around hear a few years back, we'd maybe have several hundred procedures today, and not a 2000+ load of redundancy and chaff.

Philip



Tatsu
Tatsu
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 307
This may be a little nit-picky but I recommend building the query in Query Analyzer outside the boundaries of a stored procedure and just using variables for the parameters. It's a lot easier to debug and do performance analysis and, since I am using parameters, the performance shouldn't vary when I create the procedure.

Otherwise, I think your process is excellent and I wish management-types could see the benefits of a little up-front legwork.

Bryant E. Byrd, MCDBA
Sr. SQL Server DBA/Systems Analyst
Intellithought, Inc.
bbyrd@intellithought.com

Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Robert W Marda
Robert W Marda
SSC Eights!
SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)

Group: General Forum Members
Points: 984 Visits: 82
I often created my queries and tested them before turning them into stored procedures. I still do this on occassion, however if I know this will be a stored procedure then I simply create it from the start as a stored procedure and work with it from there.

This saves me the trouble of saving the query in Query Analyzer. The stored procedure is stored in the database and the database is backed up every night.

Also, if you happen to need to use the datatypes image, ntext, or text you must create a stored procedure since you can't declare them as local variables. If someone knows a way around this I would love to know it but when I try to use the keyword DECLARE for a variable with datatype image, ntext, or text I get the following error:

Server: Msg 2739, Level 16, State 1, Line 1
The text, ntext, and image data types are invalid for local variables.

Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.



Robert W. Marda
SQL Programmer
Ipreo

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search