Solved

Portion of stored proc is removed

Posted on 2014-09-11
7
81 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:Tom Knowlton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 66

Expert Comment

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

Author Comment

by:Tom Knowlton
ID: 40317185
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
ID: 40317187
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 166 total points
ID: 40317194
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 66

Assisted Solution

by:Jim Horn
Jim Horn earned 167 total points
ID: 40317199
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 51

Accepted Solution

by:
Vitor Montalvão earned 167 total points
ID: 40317257
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:Tom Knowlton
ID: 40318488
Thank you everyone
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

617 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