Solved

Portion of stored proc is removed

Posted on 2014-09-11
7
76 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
ID: 40317181
What is your question?  We suck at mind reading.
0
 
LVL 5

Author Comment

by: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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 65

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 46

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:knowlton
ID: 40318488
Thank you everyone
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

911 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

22 Experts available now in Live!

Get 1:1 Help Now