Solved

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

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

Suggested Solutions

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 article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

710 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