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 12»»

Question of the Day for 10 Feb 2006 Expand / Collapse
Author
Message
Posted Monday, February 6, 2006 10:05 PM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
Comments posted to this topic are about the Question of the Day for 10 Feb 2006 posted at http://www.sqlservercentral.com/testcenter/qod.asp?QuestionID=706.
Post #256250
Posted Friday, February 10, 2006 3:14 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 9, 2010 6:42 AM
Points: 570, Visits: 14

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

Post #257397
Posted Friday, February 10, 2006 3:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 8, 2010 4:54 PM
Points: 23, Visits: 3
Is this an error in SQL Server or can it be used for something useful?
Post #257410
Posted Friday, February 10, 2006 6:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 3:27 PM
Points: 1,394, Visits: 214

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

 




Post #257435
Posted Friday, February 10, 2006 7:28 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 16, 2013 8:08 AM
Points: 1,987, Visits: 210
I'm with Mads on this one, what is the purpose of the second store procedure? Does anyone really use this feature?
Post #257483
Posted Friday, February 10, 2006 8:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, December 16, 2006 3:56 AM
Points: 29, 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.
Post #257546
Posted Friday, February 10, 2006 9:12 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: Monday, November 17, 2014 1:28 PM
Points: 3,189, Visits: 1,271

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




Post #257570
Posted Friday, February 10, 2006 9:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, December 16, 2006 3:56 AM
Points: 29, Visits: 1
Thanks for the reply. I wasn't sure what to look under in BOL so thought I'd start here.
Post #257574
Posted Friday, February 10, 2006 9:26 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, June 5, 2012 12:03 PM
Points: 117, 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?
Post #257577
Posted Friday, February 10, 2006 1:26 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 2:58 PM
Points: 1,141, 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)
Post #257672
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse