T-SQL

Question

Post reply

T-SQL

TheRedneckDBA

SSChampion

Points: 14001
More actions
November 13, 2008 at 11:09 pm

#196066

Comments posted to this topic are about the item T-SQL
The Redneck DBA

Viewing 15 posts - 1 through 15 (of 22 total)

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

Iggy-SQL SSCarpal Tunnel Points: 4169 More actions · Answer 1

Nice! I almost got tricked. If you had put

1 Test Policy 150000.00

2 Policy1 250000.00

3 0.00

in the answer, I would have chosen it, but because this answer doesn't exist, it makes me re-read the query again carefully and noticed that the INSERT is actually part of the stored procedure. :hehe:

Urbis, an urban transformation company

Marius Els Right there with Babe Points: 763 More actions · Answer 2

As i read through the code, i didn't even realize that the second insert could possibly have been intented to be outside the sp, so in my mind the table had to contain 4 records as the sp was executed 2 times, taking into account that the sp can be executed without any values passed into it due to the default values on the parameters.

I was actually looking for other kinds of traps 😉

sqlizer SSCrazy Points: 2120 More actions · Answer 3

Nice Very tricky. I ran each statement separately. I answer it as TABLEA. I read comments posted and tried again, then only I got TABLEC.

WayneS SSC Guru Points: 95460 More actions · Answer 4

Iggy (11/13/2008)
Nice! I almost got tricked. If you had put
1 Test Policy 150000.00
2 Policy1 250000.00
3 0.00
in the answer, I would have chosen it, but because this answer doesn't exist, it makes me re-read the query again carefully and noticed that the INSERT is actually part of the stored procedure. :hehe:

I would have fallen into the same boat you would have.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

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.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 6

I don't agree with calling this tricky. It's only tricky if you start making assumption about what might have been intended as opposed to what is actually there.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Marius Els Right there with Babe Points: 763 More actions · Answer 7

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

cy-dba SSCarpal Tunnel Points: 4179 More actions · Answer 8

Nothing like a good trick question on a Friday morning to keep you on your toes!

Cliff Jones SSChampion Points: 10547 More actions · Answer 9

slange (11/14/2008)
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.

When you script out stored procedures in SQL 2005, it does not add the 'GO' to the bottom of the script as it did in SQL 2000. Consequently we have developers who script out their stored procedures for the install script and forget to add the 'GO' and end up with bonus commands in their stored procedure (if I don't catch it). So I also made it a best practice to use the begin and end to denote the stored procedure body. This question illustrates the problem.

Thanks for the question.

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 10

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!

The Redneck DBA

Cliff Jones SSChampion Points: 10547 More actions · Answer 11

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.

Rob Shane Valued Member Points: 70 More actions · Answer 12

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.

Kevin SSCarpal Tunnel Points: 4962 More actions · Answer 13

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!

Cliff Jones SSChampion Points: 10547 More actions · Answer 14

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.