Portion of stored proc is removed

After the stored proc code below runs and I do a "SCRIPT" -  "CREATE" in a new sql query window, the one portion of the code that drops the stored proc if it exists is missing.  I guess this is normal?  But I don't want this to happen.  Is there a way to keep SQL Server from deleting that part of the code?

--this gets removed after the code further down runs:
IF OBJECTPROPERTY(object_id('dbo.DHD_GetTicketDetailsAttachmentList'), N'IsProcedure') = 1
DROP PROCEDURE [dbo].[DHD_GetTicketDetailsAttachmentList]
GO

Open in new window


USE [CoDGWEdwardBus]
GO

/****** Object:  StoredProcedure [dbo].[DHD_GetTicketDetailsAttachmentList]    Script Date: 09/11/2014 08:20:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF OBJECTPROPERTY(object_id('dbo.DHD_GetTicketDetailsAttachmentList'), N'IsProcedure') = 1
DROP PROCEDURE [dbo].[DHD_GetTicketDetailsAttachmentList]
GO
----drop if exists

CREATE PROCEDURE [dbo].[DHD_GetTicketDetailsAttachmentList]
	@TicketNumber NVARCHAR(32) = NULL
	
AS  
	SELECT      		
		c.CallNumber as TicketNumber, -- Ticket Number	String		TRUE		SCCalls.CallNumber --> DBFileHeader.LinkID	
		f.LastUpdate AS DateTime,  --Date/Time 	Date/Time	Date/Time stamp for retrieving delta information, if applicable	DBFiles.LastUpdate
		f.DBFileID AS Number, --  Attachment: Number	String			Identifier	DBFiles.DBFileID
		f.FileName, -- Attachment: Filename	String			Document name	DBFiles.FileName
		f.DBFileTypeID as AttachmentType, --file type
		f.Description, -- Brief description of attachment	DBFiles.Description	
		f.NetworkFilePath AS Link, --Link to stored doc on e-a server	DBFiles.NetworkFilePath Name "_" +  DBFiles.DBFileID + "_" + DBFiles.Revision + Extension	
		'Success' as [Status],
		'Status Description' as [StatusDescription]
		
	FROM SCCalls c
		LEFT JOIN DBFileHeader fh ON c.CallID = fh.LinkID AND fh.DBFileLinkTypeID = 2
        LEFT JOIN DBFileLinks fl ON fh.DBFileHeaderID = fl.DBFileHeaderID
        LEFT JOIN DBFiles f ON fl.DBFileID = f.DBFileID	 
        
	WHERE c.CallNumber = @TicketNumber





GO

Open in new window

LVL 5
Tom KnowltonWeb developerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
What is your question?  We suck at mind reading.
0
Tom KnowltonWeb developerAuthor Commented:
Ha ha ...  I realized that I forgot to actually ask the question....

I corrected this.

The portion of the code that drops the stored proc if it exists -- gets removed after CREATE code runs.
0
Lee SavidgeCommented:
Save your stored procedures as SQL files in the file system and include that code at the top. Then when you open it and run it, it will drop if exists and the recreate. SQL won't keep that.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Lee SavidgeCommented:
For example, I keep all my stored procedures as files on my machine. The code in them reads like this:

use myDatabase
go

if exists (select * from sysobjects where name = N'myStoredProcedure' and type = N'P')
    drop myStoredProcedure
go

create myStoredProcedure (myVar1 nvarchar(100))
as
begin
    --- Do my SQL here
end
go

grant execute on myStoredProcedure to public -- I may not use public, I may specify  something else
go

Open in new window


This way, I don't have to mess around expanding trees to get my code, I just open a file, edit, execute and save.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Lee has answer #1.   The only answer #2 I can think of is to use ALTER PROCEDURE, but that'll fail if the proc doesn't already exist.

Also (someone correct me if I'm wrong) doing a DROP/CREATE may impact security privs granted to users on that object, whereas ALTER would not.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
The DROP PROCEDURE command is not part of the Stored Procedure.
MSSQL Management Studio do that automatically when we request a script of the objects.
So when you request a script only for the creation of a SP it won't comes with the DROP PROCEDURE part.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tom KnowltonWeb developerAuthor Commented:
Thank you everyone
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.