<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Jason Shadonix
»
T-SQL
23 posts, Page 2 of 3
««
1
2
3
»»
T-SQL
Rate Topic
Display Mode
Topic Options
Author
Message
Jason Shadonix
Jason Shadonix
Posted Friday, November 14, 2008 8:38 AM
Ten Centuries
Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:49 AM
Points: 1,409,
Visits: 2,032
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
Cliff Jones
Cliff Jones
Posted Friday, November 14, 2008 9:09 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 2:31 PM
Points: 3,418,
Visits: 3,438
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
Rob Shane
Rob Shane
Posted Friday, November 14, 2008 9:46 AM
Valued Member
Group: General Forum Members
Last Login: Thursday, February 02, 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
kevin.l.williams
kevin.l.williams
Posted Friday, November 14, 2008 9:47 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 2,808,
Visits: 1,129
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
Cliff Jones
Cliff Jones
Posted Friday, November 14, 2008 10:02 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 2:31 PM
Points: 3,418,
Visits: 3,438
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
tzara
tzara
Posted Friday, November 14, 2008 11:19 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, May 20, 2013 12:41 PM
Points: 113,
Visits: 56
>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
jfgoude
jfgoude
Posted Friday, November 14, 2008 11:45 AM
SSC Eights!
Group: General Forum Members
Last Login: Today @ 8:17 AM
Points: 884,
Visits: 224
much too easy !!
Post #602992
Christian Buettner-167247
Christian Buettner-167247
Posted Sunday, November 16, 2008 9:20 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 1:16 AM
Points: 2,547,
Visits: 3,647
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
Marius Els
Marius Els
Posted Sunday, November 16, 2008 11:06 PM
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, October 31, 2012 3:59 AM
Points: 215,
Visits: 166
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
Anipaul
Anipaul
Posted Monday, November 17, 2008 5:29 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Tuesday, June 11, 2013 5:01 AM
Points: 4,814,
Visits: 1,343
Excellent question.. we should have more questions like this one. Kudos to Jason...
Post #603581
« Prev Topic
|
Next Topic »
23 posts, Page 2 of 3
««
1
2
3
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
