Solved

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

Posted on 2008-10-16
3
183 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

628 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