14
I want to update several tables using one stored procedure.
How can i do this I mean the syntax.etc. declaration etc.
I know the basic syntax as below
CREATE PROCEDURE <Procedure_Name , sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_P aram1, , int> = <Default_Value_ For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_P aram2, , int> = <Default_Value_ For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO
my SQL where i now use the query as Below to update needs to be in stored procedure please help
BEGIN TRANSACTION
COMMIT (if no errors)
or
ROLLBACK (if errors)
UPDATE dbo.Payment_Enr ollment_AIMS
SET dbo.Payment_Enr ollment_AIMS.[PROJ END DT]= dbo.V_Payment_e nrollment_end_d t_AIMS.DTE_PROJ _END
FROM dbo.Payment_Enr ollment_AIMS JOIN dbo.V_Payment_e nrollment_end_d t_AIMS
ON dbo.Payment_Enr ollment_AIMS.En rollment_ID=dbo .V_Payment_enro llment_end_dt_A IMS.Enrollment_ ID
where dbo.Payment_Enr ollment_AIMS.JC _ID = dbo.V_Payment_e nrollment_end_d t_AIMS.JC_ID
and dbo.Payment_Enr ollment_AIMS.[PROJ START DT] =dbo.V_Payment_ enrollment_end_ dt_AIMS.[PROJ START DT]
and dbo.Payment_Enr ollment_AIMS.[Grant]=dbo.V_Payment_ enrollment_end_ dt_AIMS.[Grant]
and dbo.Payment_Enr ollment_AIMS.TE RM= dbo.V_Payment_e nrollment_end_d t_AIMS.TERM
UPDATE dbo.Payment_Pla cement_AIMS
SET dbo.Payment_Pla cement_AIMS.[EMP END DT]= dbo.V_Payment_p lacement_end_dt _AIMS.DTE_END_E MPLR
FROM dbo.Payment_Pla cement_AIMS JOIN dbo.V_Payment_p lacement_end_dt _AIMS
ON dbo.Payment_Pla cement_AIMS.JC_ ID = dbo.V_Payment_p lacement_end_dt _AIMS.JC_ID
where dbo.Payment_Pla cement_AIMS.[EMP START DT] =dbo.V_Payment_ placement_end_d t_AIMS.[EMP START DT]
and dbo.Payment_Pla cement_AIMS.[Grant]=dbo.V_Payment_ placement_end_d t_AIMS.CDE_PROJ
and dbo.Payment_Pla cement_AIMS.HOU RS= dbo.V_Payment_p lacement_end_dt _AIMS.HOURS
and dbo.Payment_Pla cement_AIMS.WAG E=dbo.V_Payment _placement_end_ dt_AIMS.WAGE
UPDATE dbo.Payment_Act ivity_AIMS
SET dbo.Payment_Act ivity_AIMS.[ACTV END DT]= dbo.V_Payment_a ctivity_end_dt_ AIMS.DTE_END_AC TV
FROM dbo.Payment_Act ivity_AIMS JOIN dbo.V_Payment_a ctivity_end_dt_ AIMS
ON dbo.Payment_Act ivity_AIMS.ACTI VITY_ID =dbo.V_Payment_ activity_end_dt _AIMS.ACTIVITY_ ID
where dbo.Payment_Act ivity_AIMS.JC_I D = dbo.V_Payment_a ctivity_end_dt_ AIMS.JC_ID
and dbo.Payment_Act ivity_AIMS.[ACTV START DT] = dbo.V_Payment_a ctivity_end_dt_ AIMS.[ACTV START DT]
and dbo.Payment_Act ivity_AIMS.[ACTIVITY CODE]= dbo.V_Payment_a ctivity_end_dt_ AIMS.[ACTIVITY CODE]
and dbo.Payment_Act ivity_AIMS.PROG RAM= dbo.V_Payment_a ctivity_end_dt_ AIMS.PROGRAM
UPDATE dbo.Payment_Act ivity_AIMS
SET dbo.Payment_Act ivity_AIMS.[ACTV END DT]= dbo.V_Payment_a ctivity_end_dt_ AIMS.DTE_END_AC TV
FROM dbo.Payment_Act ivity_AIMS JOIN dbo.V_Payment_a ctivity_end_dt_ AIMS
ON dbo.Payment_Act ivity_AIMS.ACTI VITY_ID =dbo.V_Payment_ activity_end_dt _AIMS.ACTIVITY_ ID
where dbo.Payment_Act ivity_AIMS.JC_I D = dbo.V_Payment_a ctivity_end_dt_ AIMS.JC_ID
and dbo.Payment_Act ivity_AIMS.[ACTV START DT] = dbo.V_Payment_a ctivity_end_dt_ AIMS.[ACTV START DT]
and dbo.Payment_Act ivity_AIMS.[ACTIVITY CODE]= dbo.V_Payment_a ctivity_end_dt_ AIMS.[ACTIVITY CODE]
and dbo.Payment_Act ivity_AIMS.PROG RAM= dbo.V_Payment_a ctivity_end_dt_ AIMS.PROGRAM
UPDATE dbo.Payment_Sub project_Retenti on_AIMS
SET dbo.Payment_Sub project_Retenti on_AIMS.[SUBPROJ END DT]= dbo.V_Payment_S ubproject_Reten tion_end_dt_AIM S.DTE_END_PROJ_ SUB,
dbo.Payment_Sub project_Retenti on_AIMS.PROJ_EN D_DATE= dbo.V_Payment_S ubproject_Reten tion_end_dt_AIM S.DTE_PROJ_END
FROM dbo.Payment_Sub project_Retenti on_AIMS JOIN dbo.V_Payment_S ubproject_Reten tion_end_dt_AIM S
ON dbo.Payment_Sub project_Retenti on_AIMS.Subproj ect_Retention_I D =dbo.V_Payment_ Subproject_Rete ntion_end_dt_AI MS.Subproject_R etention_ID
where dbo.Payment_Sub project_Retenti on_AIMS.JC_ID = dbo.V_Payment_S ubproject_Reten tion_end_dt_AIM S.JC_ID
and dbo.Payment_Sub project_Retenti on_AIMS.[SUBPROJ START DT] = dbo.V_Payment_S ubproject_Reten tion_end_dt_AIM S.[SUBPROJ START DT]
and dbo.Payment_Sub project_Retenti on_AIMS.[SUB PROJECT]= dbo.V_Payment_S ubproject_Reten tion_end_dt_AIM S.[SUB PROJECT]
and dbo.Payment_Sub project_Retenti on_AIMS.PROJ_ST ART_DATE= dbo.V_Payment_S ubproject_Reten tion_end_dt_AIM S.DTE_PROJ_BEGI N
UPDATE dbo.Payment_Pla cement_Retentio n_AIMS
SET dbo.Payment_Pla cement_Retentio n_AIMS.[EMP END DT]= V_Payment_Place ment_Retention_ end_dt_AIMS.DTE _END_EMPLR
FROM dbo.Payment_Pla cement_Retentio n_AIMS JOIN V_Payment_Place ment_Retention_ end_dt_AIMS
ON dbo.Payment_Pla cement_Retentio n_AIMS.Placemen t_Retention_ID =V_Payment_Plac ement_Retention _end_dt_AIMS.Pl acement_Retenti on_ID
where dbo.Payment_Pla cement_Retentio n_AIMS.JC_ID = V_Payment_Place ment_Retention_ end_dt_AIMS.JC_ ID
and dbo.Payment_Pla cement_Retentio n_AIMS.[Grant] = V_Payment_Place ment_Retention_ end_dt_AIMS.[Grant]
and dbo.Payment_Pla cement_Retentio n_AIMS.[EMP START DT]= V_Payment_Place ment_Retention_ end_dt_AIMS.[EMP START DT]
UPDATE dbo.Payment_Pla cement_Retentio n_AIMS
SET dbo.Payment_Pla cement_Retentio n_AIMS.PROJ_END _DATE = dbo.V_Payment_P lacement_Retent ion_proj_end_dt _AIMS.DTE_PROJ_ END
FROM dbo.Payment_Pla cement_Retentio n_AIMS JOIN dbo.V_Payment_P lacement_Retent ion_proj_end_dt _AIMS
ON dbo.Payment_Pla cement_Retentio n_AIMS.Placemen t_Retention_ID =dbo.V_Payment_ Placement_Reten tion_proj_end_d t_AIMS.Placemen t_Retention_ID
where dbo.Payment_Pla cement_Retentio n_AIMS.JC_ID = dbo.V_Payment_P lacement_Retent ion_proj_end_dt _AIMS.JC_ID
and dbo.Payment_Pla cement_Retentio n_AIMS.[Grant] = dbo.V_Payment_P lacement_Retent ion_proj_end_dt _AIMS.[Grant]
and dbo.Payment_Pla cement_Retentio n_AIMS.PROJ_STA RT_DATE= dbo.V_Payment_P lacement_Retent ion_proj_end_dt _AIMS.PROJ_STAR T_DATE
and dbo.Payment_Pla cement_Retentio n_AIMS.CDE_PROJ _TERM= dbo.V_Payment_P lacement_Retent ion_proj_end_dt _AIMS.CDE_PROJ_ TERM
