SQL Server 2005 Stored Procedure Confusion

How to Dynamically set one field to another fields value...

Page 1 of 1

2 Replies - 790 Views - Last Post: 20 June 2008 - 01:00 PM Rate Topic: -----

#1 Mr_Mom  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 30
  • Joined: 21-May 08

SQL Server 2005 Stored Procedure Confusion

Posted 20 June 2008 - 10:28 AM

Hello,

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,



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())




Is This A Good Question/Topic? 0
  • +

Replies To: SQL Server 2005 Stored Procedure Confusion

#2 Mr_Mom  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 30
  • Joined: 21-May 08

Re: SQL Server 2005 Stored Procedure Confusion

Posted 20 June 2008 - 12:06 PM

View PostMr_Mom, on 20 Jun, 2008 - 11:28 AM, said:

Hello,

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,



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:
DECLARE @PrevPlanDate smalldatetime 
SELECT @PrevPlanDate = PlanDate FROM tblPlan WHERE PlanID = @oldPlanID



AND

   ,@PrevPlanDate



unfortunately I am getting the following error:

"Insert Error: Column name or number of supplied values does not match table definition."

Ideas??

Thanks,
Was This Post Helpful? 0
  • +
  • -

#3 Mr_Mom  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 30
  • Joined: 21-May 08

Re: SQL Server 2005 Stored Procedure Confusion

Posted 20 June 2008 - 01:00 PM

Nevermind, I got it!

DECLARE @PrevPlanDate smalldatetime
SELECT @PrevPlanDate = (SELECT PlanDate FROM tblPlan WHERE PlanID = @oldPlanID)



That did the trick.

Thanks anyway,

Patrick
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1