Solved

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

Posted on 2008-10-16
3
178 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle query help 18 99
Unable to insert second line output line in ODBC connection 4 55
Unable to save view in SSMS 21 59
Make query more efficient 1 16
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.…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
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, f…

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

19 Experts available now in Live!

Get 1:1 Help Now