Join 118,878 Programmers for FREE! Ask your question and get quick answers from experts. There are 1,958 online right now! We've got more than 500 tutorials and 2,000 snippets. Join and find out why Dream.In.Code is the #1 programming help community on the internet! Registration is fast and FREE... Join Now!
I am trying to modify an existing Stored Procedure to include two New fields(COMMENTED Below) from the asp.net form that is the front side. When this procedure is run I need [PrevPlanDate] to be populated with whatever is in the PlanDate field(COMMENTED Below). So if the PlanDate is 8/13/2008 and this gets run then PrevPlanDate needs to become 8/13/2008. The format below is confusing for me so I am unsure how to proceed here. They seem to just go in order of the fields...does that mean I can add a SELECT call in PrevPlanDate's position like so: [PlanDate]??
OR
Do I need to Declare a variable to hold PlanDate and then plug the variable into the INSERT in PrevPlanDate's position?
Any guidance is greatly appreciated.
Thank you,
CODE
ALTER PROCEDURE [dbo].[spReplicatePlan] -- Add the parameters for the stored procedure here @oldPlanID int, @userID as varchar(50) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- COPY PLAN DECLARE @newPlanID int
--select [ClientID],[CMOSocialWorkerID],[CMONurseID],[EconomicSupportWorkerID],[TargetGroupID],[CMOEnrollmentDate],[MedicaidNumber],[MedicareNumber],[MedicareA],[MedicareAEffDate],[MedicareB],[MedicareBEffDate],[MedicareDPlan],[PrivateInsurance],[PrivateInsuranceName],[VA],[OthersInHousehold],[Pets],[Allergies],[PlanDate],[SixMonthReviewDate],[FunctionalScreenDate],[GuardianOfPerson],[GuardianOfPersonName],[GuardianOfPersonPhone],[GuardianOfEstate],[GuardianOfEstateName],[GuardianOfEstatePhone],[RepresentativePayee],[RepresentativePayeeName],[RepresentativePayeePhone],[POAHC],[POAHCActivated],[POAHCName],[POAHCPhone],[POAF],[POAFName],[POAFPhone],[CodeStatusPOLST],[CodeStatus],[MinimumContactSchedule],[SixMonthWaiverExplanation],[SelfDirectedSupportServices],[ReviewedMemberHandbook],[ReasonWhyNotReviewed],[ReleaseOfInformationUpdated],[RestrictiveMeasures],[ReviewAFHAgreement],[DateAFHAgreementLastReviewed],[ReviewSixMonthCrisisPlan],[DateSixMonthCrisisPlanLastReviewed],[NursingHomeCareConferencesDate1],[NursingHomeCareConferencesDate2],[POAForHealthcareCompleted],[CommentsRegardingReviews],[AttendeesAtPlanningSession],[MemberAgreesDisagreesWithPlan],[ReasonMemberDisagrees],[DateMCP_ISPMailed],[MailedSignaturePageToMember],[MailedSignaturePageToGuardian], [ReceivedSignedSignaturePageFromMember],[ReceivedSignedSignaturePageFromGuardian],'False', @userID, getdate() from tblPlan where planID = @oldPlanID insert into tblPlan select [EnrollmentID] ,[ClientID] ,[VA] ,[OthersInHousehold] ,[Pets] ,[Allergies] ,getdate() -- THIS IS PlanDate ,dateadd(month, 6, getdate()) ,[FunctionalScreenDate] ,[POAHC] ,[POAHCProvidedInfo] ,[POAHCCopyInFile] ,[POAHCActivated] ,[CodeStatusPOLST] ,[CodeStatus] ,[ProtectivePlacement] ,[PPReviewDate] ,[MinimumContactSchedule] ,[MinimumContactScheduleOther] ,[SixMonthWaiverExplanation] ,[SelfDirectedSupportServices] ,[SelfDirectedSupportServicesExplained] ,[ReviewedMemberHandbook] ,[ReasonWhyNotReviewed] ,[ReleaseOfInformationUpdated] ,[RestrictiveMeasures] ,[ReviewAFHAgreement] ,[DateAFHAgreementLastReviewed] ,[ReviewSixMonthCrisisPlan] ,[DateSixMonthCrisisPlanLastReviewed] ,[NursingHomeCareConferencesDate1] ,[NursingHomeCareConferencesDate2] ,[ResidentialToolReviewedID] ,[CommentsRegardingReviews] ,[AttendeesAtPlanningSession] ,[MemberAgreesDisagreesWithPlan] ,[ReasonMemberDisagrees] ,[DateMCP_ISPMailed] ,[MailedSignaturePageToMember] ,[DateMailedToMember] ,[MailedSignaturePageToGuardian] ,[DateMailedToGuardian] ,[ReceivedSignedSignaturePageFromMember] ,[DateReceivedFromMember] ,[ReceivedSignedSignaturePageFromGuardian] ,[DateReceivedFromGuardian] ,'False' ,@userID ,getdate() ,@userID ,getdate() ,[PrevPlanDate] -- NEW field in Question ,[PrivateInsurance] -- Another NEW field from tblPlan where planID = @oldPlanID set @newPlanID = (select scope_identity())
I am trying to modify an existing Stored Procedure to include two New fields(COMMENTED Below) from the asp.net form that is the front side. When this procedure is run I need [PrevPlanDate] to be populated with whatever is in the PlanDate field(COMMENTED Below). So if the PlanDate is 8/13/2008 and this gets run then PrevPlanDate needs to become 8/13/2008. The format below is confusing for me so I am unsure how to proceed here. They seem to just go in order of the fields...does that mean I can add a SELECT call in PrevPlanDate's position like so: [PlanDate]??
OR
Do I need to Declare a variable to hold PlanDate and then plug the variable into the INSERT in PrevPlanDate's position?
Any guidance is greatly appreciated.
Thank you,
CODE
ALTER PROCEDURE [dbo].[spReplicatePlan] -- Add the parameters for the stored procedure here @oldPlanID int, @userID as varchar(50) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- COPY PLAN DECLARE @newPlanID int
--select [ClientID],[CMOSocialWorkerID],[CMONurseID],[EconomicSupportWorkerID],[TargetGroupID],[CMOEnrollmentDate],[MedicaidNumber],[MedicareNumber],[MedicareA],[MedicareAEffDate],[MedicareB],[MedicareBEffDate],[MedicareDPlan],[PrivateInsurance],[PrivateInsuranceName],[VA],[OthersInHousehold],[Pets],[Allergies],[PlanDate],[SixMonthReviewDate],[FunctionalScreenDate],[GuardianOfPerson],[GuardianOfPersonName],[GuardianOfPersonPhone],[GuardianOfEstate],[GuardianOfEstateName],[GuardianOfEstatePhone],[RepresentativePayee],[RepresentativePayeeName],[RepresentativePayeePhone],[POAHC],[POAHCActivated],[POAHCName],[POAHCPhone],[POAF],[POAFName],[POAFPhone],[CodeStatusPOLST],[CodeStatus],[MinimumContactSchedule],[SixMonthWaiverExplanation],[SelfDirectedSupportServices],[ReviewedMemberHandbook],[ReasonWhyNotReviewed],[ReleaseOfInformationUpdated],[RestrictiveMeasures],[ReviewAFHAgreement],[DateAFHAgreementLastReviewed],[ReviewSixMonthCrisisPlan],[DateSixMonthCrisisPlanLastReviewed],[NursingHomeCareConferencesDate1],[NursingHomeCareConferencesDate2],[POAForHealthcareCompleted],[CommentsRegardingReviews],[AttendeesAtPlanningSession],[MemberAgreesDisagreesWithPlan],[ReasonMemberDisagrees],[DateMCP_ISPMailed],[MailedSignaturePageToMember],[MailedSignaturePageToGuardian], [ReceivedSignedSignaturePageFromMember],[ReceivedSignedSignaturePageFromGuardian],'False', @userID, getdate() from tblPlan where planID = @oldPlanID insert into tblPlan select [EnrollmentID] ,[ClientID] ,[VA] ,[OthersInHousehold] ,[Pets] ,[Allergies] ,getdate() -- THIS IS PlanDate ,dateadd(month, 6, getdate()) ,[FunctionalScreenDate] ,[POAHC] ,[POAHCProvidedInfo] ,[POAHCCopyInFile] ,[POAHCActivated] ,[CodeStatusPOLST] ,[CodeStatus] ,[ProtectivePlacement] ,[PPReviewDate] ,[MinimumContactSchedule] ,[MinimumContactScheduleOther] ,[SixMonthWaiverExplanation] ,[SelfDirectedSupportServices] ,[SelfDirectedSupportServicesExplained] ,[ReviewedMemberHandbook] ,[ReasonWhyNotReviewed] ,[ReleaseOfInformationUpdated] ,[RestrictiveMeasures] ,[ReviewAFHAgreement] ,[DateAFHAgreementLastReviewed] ,[ReviewSixMonthCrisisPlan] ,[DateSixMonthCrisisPlanLastReviewed] ,[NursingHomeCareConferencesDate1] ,[NursingHomeCareConferencesDate2] ,[ResidentialToolReviewedID] ,[CommentsRegardingReviews] ,[AttendeesAtPlanningSession] ,[MemberAgreesDisagreesWithPlan] ,[ReasonMemberDisagrees] ,[DateMCP_ISPMailed] ,[MailedSignaturePageToMember] ,[DateMailedToMember] ,[MailedSignaturePageToGuardian] ,[DateMailedToGuardian] ,[ReceivedSignedSignaturePageFromMember] ,[DateReceivedFromMember] ,[ReceivedSignedSignaturePageFromGuardian] ,[DateReceivedFromGuardian] ,'False' ,@userID ,getdate() ,@userID ,getdate() ,[PrevPlanDate] -- NEW field in Question ,[PrivateInsurance] -- Another NEW field from tblPlan where planID = @oldPlanID set @newPlanID = (select scope_identity())
Okay,
I tried adding in the follwoing snippets to the above:
CODE
DECLARE @PrevPlanDate smalldatetime SELECT @PrevPlanDate = PlanDate FROM tblPlan WHERE PlanID = @oldPlanID
AND
CODE
,@PrevPlanDate
unfortunately I am getting the following error:
"Insert Error: Column name or number of supplied values does not match table definition."
20 Jun, 2008 - 10:28 AM
| 