Solved

Portion of stored proc is removed

Posted on 2014-09-11
7
75 Views
Last Modified: 2014-09-11
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

0
Comment
Question by:knowlton
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
What is your question?  We suck at mind reading.
0
 
LVL 5

Author Comment

by:knowlton
Comment Utility
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
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 166 total points
Comment Utility
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
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 167 total points
Comment Utility
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
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 167 total points
Comment Utility
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
 
LVL 5

Author Closing Comment

by:knowlton
Comment Utility
Thank you everyone
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now