Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2008-10-16
3
Medium Priority
?
190 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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

876 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