June 11, 2009 at 2:47 pm
Hi,
We have many contractors and developers in our environment ( just like any other place ). Everyone use their own header information (i.e. purpose, history, created by, projectid etc ).
I have provided documentation but that is not followed properly.
Is there any way to change the 'create stored procedure ' template on sql server client side ( or if possible on server side ), so whenever they click on create new procedure, new header info is displayed where they can fill in information.
For example:
Following header gets populated by default:
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
I would like to change it to the following. It is pain to create one and I don't blame developers if they don't want to follow it. However, it has been tremendously useful as short term contractors come and go to have all documentation in procs itself.
/*
************************************************************
** Procedure Name: CreateOrder_ForBulkShipment
** Version: 1.0
** Purpose: Create the bulkship order record for XYZ customer and for %%%
material type
**
** Input: @CustomerID int
@ProductId int
** Output: @Successflag = 0
** ErrorHandling:
select * from CentralInfoDB.dbo.Exceptionerrors
where Procedurename = 'CreateOrder_ForBulkShipment'
** Tables Updated: Orders, OrderDetails, Shipschedule
** Application: Web Order system
** Implementation: BulkShipOrders.aspx
** Revision History:
=================
**VersionReason ChangedByDate ChangeRequest
**1.0createdShaili 11/05/2008
** 2.0Modified for Shaili 06/11/2009 CR0000987
material exception
*********************************************************************
*/
Thanks,
Shaili :crazy:
June 11, 2009 at 3:10 pm
Not sure about changing the template. But as far as enforcing standards in layout, etc., you can usually just kick stuff back from the QA server till they get it right. Do what they want on Dev, but by the time they send it to QA, before the script gets run, it should go through code review.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 12, 2009 at 6:50 am
In Management Studio, check out View, Template Explorer. Scroll down & find Stored Procedures and the "Create Stored Procedure (New Menu)" script. Edit that and the next time someone clicks on new procedure, they'll see whatever you put here. But you have to enable this and distribute it to all machines to see it used.
But, they can also script their own without using your template. And, if they right click on an existing procedure and get the create script for it, it might not follow your template either.
But, it's a start.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 15, 2009 at 7:19 am
If they do what I do, and just open a connection and start typing, it won't help with that either. That's why I suggested code-review.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 15, 2009 at 10:08 am
Thanks guys for the feedback.
I agree that code review is the best thing as I myself just open up a new query and start typing 'Create procedure ..' I
I have rejected the procs for the same reason in the past. However, as it is the deadlines are so tight ( 70-75 hours usually around qa time approaching ) that we usually feel burn out by the time it is delivered to qa. I feel bad pushing code back to them for this reason when they haven't seen their kids for several weeks. So, I end up working extra to document the code for maintaining standards in my database. But I can't keep up doing it either with my work load.
So, I just felt that if something is available, developers/contractors do not have reason not to use it and I won't feel guilty pushing the procs back to them to fix which doesn't meet the standards.
Thanks a lot..
June 16, 2009 at 6:53 am
There's nothing I know of that can't be bypassed. QA needs to hold to established standards, or the standards need to be revised to say what the exceptions are. Otherwise, there's no point to QA and it's just being done to satisfy bureaucracy, which is never a good thing. Hold fast to the rules, or change the rules officially. Either one works.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 17, 2009 at 7:57 am
GSquared (6/15/2009)
If they do what I do, and just open a connection and start typing, it won't help with that either. That's why I suggested code-review.
I was going to say... "There's a template for CREATE PROCEDURE"?
I've pretty much always just popped open a connection and started typing... rarely do the templates help me much. Though having one for CREATE PROCEDURE for the folks here might help them... I keep demanding header blocks... and not getting them.
June 17, 2009 at 8:10 am
There is a template, but, like Gus says, unless they do precisely the same thing every time to create a new proc and no one ever just starts typing (same way I do it), there's no way to guarantee the use of the template.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply