Solved

Why did I lose my some of my code when I create a new stored procedure?

Posted on 2008-10-16
3
177 Views
Last Modified: 2010-03-20
Hi, When I execute this query to create a new SP, I lose my code that check and drop table.
Why is that so?  did I put this in the wrong place?  I run to alter the SP with these code and still it won't pick up.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CtsiApplication]') AND type in (N'U'))
   DROP TABLE CtsiApplication
USE [CtsiMembershipProduction]

GO
 

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CtsiApplication]') AND type in (N'U'))

   DROP TABLE CtsiApplication

--IF EXISTS(SELECT CtsiApplication FROM INFORMATION_SCHEMA.TABLES

--      WHERE TABLE_NAME = 'CtsiApplication')

GO

/****** Object:  StoredProcedure [dbo].[GetData]    Script Date: 10/16/2008 19:21:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO
 

Create PROCEDURE [dbo].[Ctsi_GetData]

AS
 
 

SELECT [dbo].[Applications].[Id]

      ,[MembershipLevel]

      ,[ProfessionalOrganizations]

      ,[Department]

      ,[Gender]

      ,[Ethnicity]

      ,[Race]

      ,[YearOfBirth]

      ,[Title_id]

      ,[dbo].[Applications].[EmailAddress]

      ,[LastName]

      ,[Firstname]

      ,[PhoneNumber]

      ,[Institution]

      ,[MailingAddress]

      ,[OfficeAddress]

      ,[TitleOther]

      ,[PhdTraineeCount]

      ,[MscTraineeCount]

      ,[InstitutionOther]

      ,[MembershipReasonOther]

      ,[RaceOther]

      ,[MailingAddressLine1]

      ,[MailingAddressLine2]

      ,[MailingAddressCity]

      ,[MailingAddressState]

      ,[MailingAddressZip]

      ,[CommunityOrganization]

	, tblDegrees.Name as [Degree 1]

	, dbo.ApplicationResearchInterests.ResearchInterestId as RchChildId--child Rch id

	, dbo.MembershipReasons.Name as [Membership Reason 1]

	, dbo.MembershipServiceRequirements.Name as [Membership Service Requirement 1]

INTO CtsiApplication

  FROM dbo.ApplicationDegrees, 

dbo.[Degrees] as tblDegrees, dbo.ApplicationMembershipReasons, dbo.ApplicationMembershipServiceRequirements,

dbo.ApplicationResearchInterests, dbo.ResearchInterests as tblRschInt, dbo.MembershipReasons,

dbo.MembershipServiceRequirements, [dbo].[Applications]

inner join (SELECT min([Id]) AS Id, [EmailAddress] from [dbo].[Applications] group by [EmailAddress]) AS B

ON B.Id = [dbo].[Applications].Id  --filter our duplicate email address record, get the lowest id

Where dbo.Applications.Id = dbo.ApplicationDegrees.ApplicationId 

and dbo.Applications.Id = dbo.ApplicationMembershipReasons.ApplicationId

and dbo.Applications.Id = dbo.ApplicationMembershipServiceRequirements.ApplicationId

and dbo.Applications.Id = dbo.ApplicationResearchInterests.ApplicationId

and dbo.ApplicationDegrees.ApplicationId = tblDegrees.Id  --Get degree name

and dbo.ApplicationMembershipReasons.MembershipReasonId = dbo.MembershipReasons.Id

and dbo.ApplicationMembershipServiceRequirements.MembershipServiceRequirementId = 

					dbo.MembershipServiceRequirements.Id

Open in new window

0
Comment
Question by:lapucca
  • 2
3 Comments
 
LVL 5

Expert Comment

by:jfmador
ID: 22737715
put your code

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CtsiApplication]') AND type in (N'U'))
   DROP TABLE CtsiApplication

in your stored procedure instead of before
0
 
LVL 5

Accepted Solution

by:
jfmador earned 500 total points
ID: 22737723
I mean just after the create SP

Exemple :

Create PROCEDURE [dbo].[Ctsi_GetData]
AS

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CtsiApplication]') AND type in (N'U'))
   DROP TABLE CtsiApplication

SELECT ......

0
 

Author Comment

by:lapucca
ID: 22737747
Perfect, thank you.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

757 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

19 Experts available now in Live!

Get 1:1 Help Now