SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Stored Procedure Comments ABOVE CREATE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

X002548
Not Just a Number

15586 Posts

Posted - 01/18/2012 :  16:06:29  Show Profile  Reply with Quote
I've ALWAYS stored my Comments, sproc descriptions, change control, etc...INSIDE my Sproc

HOW do they Get this to be generated outside of the sproc..I looked at extended properties and don't see anything

For example


USE [PSRS]
GO
/****** Object:  StoredProcedure [dbo].[DeleteCase]    Script Date: 01/18/2012 16:03:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Subi Thayamkery
-- Create date: 07/28/2008
-- Updated date: 
-- Description:	Delete Case Vehicle By VehicleId
-- =============================================
CREATE PROCEDURE [dbo].[DeleteCase]
(		
		 @UserId			VARCHAR(7) 
		,@CaseId			INT	
)
AS



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


tkizer
Almighty SQL Goddess

USA
37134 Posts

Posted - 01/18/2012 :  16:33:52  Show Profile  Visit tkizer's Homepage  Reply with Quote
I ALWAYS store them above the CREATE. I hate when the header block is below, lol. That's just how it gets stored internally. The whole thing goes in there.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/18/2012 :  16:34:07  Show Profile  Reply with Quote
This is what we usually do


CREATE PROC [dbo].[usp_UPD_myMessage_complete]
	  @myMessage_Id								nvarchar(50)			-- And Id for 'Group_Trans_Id' or 'Business_Key_Id'
	, @myMessage_Id_Type                        nvarchar(20)			-- Literal Values
	, @User										char(12)	
	, @rc int OUTPUT, @Error int OUTPUT,  @Error_Message varchar(255) OUTPUT, @Rowcount int OUTPUT
	
AS 


--
--                 Enterprise Solutions
--
--           File:      \\paerscns20\ss\GBTS_Systems_Projects\Projects\myActions\Database\SQLServer\NJROS1BBLD0304\DEV2K08\Sprocs
--           Date:      08/16/2011
--         Author:      Brett Kaiser
--         Server:      NJROS1BBLD0304\DEV2K08
--       Database:      myActions
--          Login:      myActions_User99
--		Procedure:		usp_UPD_myMessage_complete
--    Description:      Update a Row in the User_Message table
--
--   Return codes:		-1	Unsuccessful Execution 
--						0	Successful Execution
--						1	Zero rows returned or modified
--
--    Tables Used:		myMessage
--
-- Tables Created:		None
--
--
--  Row Estimates:
--	name						rows        reserved           data               index_size         unused
--	--------------------		----------- ------------------ ------------------ ------------------ ------------------
--	User_Message				0           0 KB               0 KB               0 KB               0 KB
--
-- sp_spaceused User_Message
--
--Change Log
--
-- UserId    	Date         	Description
-- -----------  --------------  ------------------------------------------------------------------------------------------
-- X168161		08-26-2011		Initial Release
-- X002548		09-08-2011		Change Inputer Parameters to be 	  
--								  @myMessage_Id	nvarchar(30)			
--								, @myMessage_Id_Type nvarchar(20)-- Literal Values 'Group_Trans_Id' or 'Business_Key_Id'
--
--
-- X002548		01-12-2011		Change the input variable parameter @myMessage_Id to be nvarchar(50) from nvarchar(30)
--
--
--

/*
--Sample Execution:



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37134 Posts

Posted - 01/18/2012 :  16:37:20  Show Profile  Visit tkizer's Homepage  Reply with Quote
Yeah and that formatting drives me nuts.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/18/2012 :  16:38:34  Show Profile  Reply with Quote
Wow

Who Gnu


--
-- Test
--

Create PROC [dbo].[Test]
AS
PRINT 'TEST'

GO


How does it know to include it with the Sproc?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37134 Posts

Posted - 01/18/2012 :  17:00:26  Show Profile  Visit tkizer's Homepage  Reply with Quote
Because it's part of the batch.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/18/2012 :  21:29:58  Show Profile  Reply with Quote
more sql server insanities

Try working on IBM mainframe

Thanks...the more you know

part of the batch...what a loose term

CREATE is the beginning of the batch

Whatever





Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 01/19/2012 :  04:00:26  Show Profile  Reply with Quote
I store comments inside the CREATE too. In a continuouse script of multiple Sprocs it is, otherwise, hard to know what is part of the "footings" of the previous or the "headings" of the next.

Not usually for a human, obviously (although the Comment may have been Cut&Pasted from somewhere else, so might have the wrong SProc name in it as a consequence), but for automatic documenting parsers and the like it can be relevant.

With each Sproc in a separate disk file then they are self contained units, so no ambiguity, but even though I do do that I also have some files that contain 2 or 3 Sprocs - e.g. the Get/Save/Delete of CRUD Sprocs and "child" Sprocs where there are alternatives depending on the parameters passed to the Parent - hence comments within the CREATE "body" for me .
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000