T-SQL

Question

T-SQL

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

tzara Say Hey Kid Points: 663 More actions · Answer 1

>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.

jfgoude SSCrazy Points: 2586 More actions · Answer 2

jfgoude

SSCrazy

Points: 2586

November 14, 2008 at 11:45 am

#898795

much too easy !!

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 3

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

Marius Els Right there with Babe Points: 762 More actions · Answer 4

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...:cool:
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

Anipaul SSC-Insane Points: 24681 More actions · Answer 5

Excellent question.. we should have more questions like this one. Kudos to Jason...

webrunner SSC-Dedicated Points: 31720 More actions · Answer 6

I got this right for the wrong reason. I didn't know that the lack of the GO was what added the second insert to the stored procedure. But the test insert just looked like part of the whole procedure after the procedure code (perhaps the lack of even a line break before the test insert tipped off my unconscious :)), so I figured the test insert will run every time the procedure runs.

Tricky question but a good lesson. I will definitely be more careful about creating procedures in the future.

Thanks!

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

lingweixuan SSC Rookie Points: 34 More actions · Answer 7

slange (11/14/2008)
I fell victim to a similair script at one point. :Whistling: 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.
declare myProc() as
begin
end
Once you stub in the solid open and closing it is very easy to fill in the body and even more difficult to accidentally leave something inside the proc.

YES, a good habit !

kapil_kk SSC-Insane Points: 21316 More actions · Answer 8

really nice question.....

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/