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 ««123»»

T-SQL Expand / Collapse
Author
Message
Posted Friday, November 14, 2008 8:38 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:47 AM
Points: 1,523, Visits: 2,203
I've had that happen also, that's what caused me to come up with this question.

We recently had a deploy script that dropped and recreated several stored procedures something like:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('SP1') AND type in ('P', 'PC'))
DROP PROCEDURE SP1
GO

CREATE PROCEDURE SP1
AS
...
GO
.
.
.

For just one of the SPs, the developers forgot the GO at the end, so one of the SPs got created with the DROP statement for the next SP in it. Apparently the SP that had it's drop added to the stored procedure before it didn't exist, so the script ran without errors. It took me quite a long time to figure out "the mysterious vanishing SP" issue that day!


Jason Shadonix
MCTS, SQL 2005
Post #602835
Posted Friday, November 14, 2008 9:09 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:36 AM
Points: 3,918, Visits: 3,638

I am not sure why they changed the scripting so that it does not include the 'GO'? It is such a subtle little change that has caused us a lot of grief.
Post #602868
Posted Friday, November 14, 2008 9:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 2, 2012 10:45 AM
Points: 54, Visits: 122
Marius Els (11/14/2008)
As a result I now make it my standard habit that after the declaration of the sp i wrap the entire body in a begin end.


that is maybe not a bad habit to develop...
it's getting it to become a habit that's the problem:D


Have you used templates in SSMS? I find them very helpful, especially for documentation headers. I also like the ability to fill in template parameters.
Post #602891
Posted Friday, November 14, 2008 9:47 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 8:30 AM
Points: 3,142, Visits: 1,261
Cliff Jones (11/14/2008)

I am not sure why they changed the scripting so that it does not include the 'GO'? It is such a subtle little change that has caused us a lot of grief.


I agree and I also miss the ability to have SSMS automatically script out the if exists drop at the beginning of the script as well!



Post #602892
Posted Friday, November 14, 2008 10:02 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:36 AM
Points: 3,918, Visits: 3,638
cmcklw (11/14/2008)
Cliff Jones (11/14/2008)

I am not sure why they changed the scripting so that it does not include the 'GO'? It is such a subtle little change that has caused us a lot of grief.


I agree and I also miss the ability to have SSMS automatically script out the if exists drop at the beginning of the script as well!


Yes, that is my other complaint. It used to be so simple to script out a single stored procedure in SQL 2000 and the result included the if exists and drop statement. It is much more cumbersome to do that in SQL 2005.
Post #602903
Posted Friday, November 14, 2008 11:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 7, 2014 4:37 PM
Points: 126, Visits: 62
>noticed that the INSERT is actually part of the stored procedure.

That part was obvious. I was distracted by the possible NULL (or rather empty value) on the insert, and thinking about that. still got it wrong, just not sure this example focused on the point you wanted to make; could be composed better.

also, are you guys really faced with this issue? for what it's worth, i've never confused what statement are in, or not in, the procedure.

>scripting

ah, i see why you're annoyed.
Post #602968
Posted Friday, November 14, 2008 11:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 3:24 AM
Points: 1,144, Visits: 299
much too easy !!
Post #602992
Posted Sunday, November 16, 2008 9:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 1:05 AM
Points: 2,842, Visits: 3,875
Thanks for the question, the batch separator can cause some headaches for newbies.
But I have some suggestions on how to improve your code:
1. Do not use "sp_" as stored procedure prefix
2. Specify the schema when referring to objects
3. Explicitly specify nullability for all columns in the table declaration.
4. All tables should have a primary key.

I know this is only test code, but the rules should always apply, since non-experienced users easily pick up bad coding styles.


Best Regards,
Chris Büttner
Post #603343
Posted Sunday, November 16, 2008 11:06 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 10, 2014 12:43 AM
Points: 217, Visits: 170
Rob Shane (11/14/2008)
Marius Els (11/14/2008)
As a result I now make it my standard habit that after the declaration of the sp i wrap the entire body in a begin end.


that is maybe not a bad habit to develop...
it's getting it to become a habit that's the problem:D


Have you used templates in SSMS? I find them very helpful, especially for documentation headers. I also like the ability to fill in template parameters.


Hi Rob

I actually haven't used the template feature that much, yet.
I'm so used to typing out the sp by hand, I haven't kept the template features in mind.
thanks
Post #603466
Posted Monday, November 17, 2008 5:29 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:19 AM
Points: 5,371, Visits: 1,391
Excellent question.. we should have more questions like this one. Kudos to Jason...


Post #603581
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse