Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Stored Procedure Development Cycle Expand / Collapse
Author
Message
Posted Friday, August 29, 2003 12:00 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
Post #15796
Posted Wednesday, September 3, 2003 1:06 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
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"
Post #77244
Posted Wednesday, September 3, 2003 1:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:31 AM
Points: 2,909, Visits: 1,837
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
Post #77245
Posted Wednesday, September 3, 2003 7:46 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 8:25 PM
Points: 649, Visits: 210
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





Post #77246
Posted Wednesday, September 3, 2003 9:09 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
Post #77247
Posted Wednesday, September 3, 2003 12:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 14, 2003 12:00 AM
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




Post #77248
Posted Wednesday, September 3, 2003 1:15 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 2:45 PM
Points: 6,794, Visits: 1,906
.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
Post #77249
Posted Wednesday, September 3, 2003 1:22 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 8:25 PM
Points: 649, Visits: 210
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





Post #77250
Posted Thursday, September 4, 2003 8:56 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 12:31 PM
Points: 295, Visits: 282
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
Post #77251
Posted Thursday, September 4, 2003 9:09 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
Post #77252
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse