SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Question of the Day for 10 Feb 2006


Question of the Day for 10 Feb 2006

Author
Message
Jan Wennemann-148510
Jan Wennemann-148510
Mr or Mrs. 500
Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)

Group: General Forum Members
Points: 572 Visits: 15

I can't execute the "second one". I have two different SELECT-Statement but it always executes the first one (in Query Analyzer).

BTW: The code looks frightening

jan


Mads Holm-152675
Mads Holm-152675
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 3
Is this an error in SQL Server or can it be used for something useful?
sschissler
sschissler
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1396 Visits: 245

To execute the second part of the stored procedure you need to include the 2.

exec SelectTest -- will return the select from the first query

exec SelectTest;2 -- will return the select from the second query





Richard Sisk
Richard Sisk
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 Visits: 210
I'm with Mads on this one, what is the purpose of the second store procedure? Does anyone really use this feature?
Michael Eller
Michael Eller
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 1
Can someone point me in the direction of an explanation of the syntax involved here. I'm new to SQL Server and am trying to learn everything I can which is why I always try the question of the day. I'd like to figure out more about the ;2 part of the syntax.
kevin.l.williams
kevin.l.williams
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3413 Visits: 1323

From BOL

";number

Is an optional integer used to group procedures of the same name so they can be dropped together with a single DROP PROCEDURE statement. For example, the procedures used with an application called orders may be named orderproc;1, orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the entire group. If the name contains delimited identifiers, the number should not be included as part of the identifier; use the appropriate delimiter around procedure_name only."

-Kevin





Michael Eller
Michael Eller
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 1
Thanks for the reply. I wasn't sure what to look under in BOL so thought I'd start here.
Dennis D. Allen
Dennis D. Allen
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 163
If you check BOL for CREATE PROCEDURE you will find it...

You could use it for test isolation. Say you are upgrading a procedure that is used in a few locations of an application, and you want to target a specific section in testing for using the new version of the procedure... Or in some cases, more then one developer shares a test database, and you need different versions of the same procedure during development.

You could also use it (instead of using fully independently named procedures) to place different optimized version of the same procedure together.

Anyone know if there is a performance hit from having multiple versions in the same procedure?
SA-1
SA-1
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1519 Visits: 944
What about the query execution plan??? Does this create only one plan or more than one (based on the number of ;numbers within the procedure)
Fal
Fal
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 Visits: 1803

My initial thoughts on reading this was "1 Procedure, the second generates an error". Finding no option for this, I went for "None, a error will be raised."

Reading the explantion, I put this through Server Management Studio to see this procedure that has two lots of code, only to get: None - error raised. Specifically "Msg 102, Level 15, State 1, Procedure SelectTest, Line 1 Incorrect syntax near ';'"

I refreshed, did it again, etc, but I still have no procs at all - let along 1 proc with both codes!

So I shut down Management Studio, restarted, checked, executed code, and got "Command(s) completed successfully." But still no procs! Indeed, I can keep on running it with no DROP statements and it will always be successful. (But with no procs.

So, what am I missing?


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search