Solved

Portion of stored proc is removed

Posted on 2014-09-11
7
77 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 47

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
tempdb log contention 16 38
SQL Server stored proc 2 19
SQL log file keeps growing despite getting successful log backups 4 47
SSRS  - Dropdown with Null 3 24
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 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