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_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , 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_Enrollment_AIMS
SET dbo.Payment_Enrollment_AIMS.[PROJ END DT]= dbo.V_Payment_enrollment_end_dt_AIMS.DTE_PROJ_END
FROM dbo.Payment_Enrollment_AIMS JOIN dbo.V_Payment_enrollment_end_dt_AIMS
ON dbo.Payment_Enrollment_AIMS.Enrollment_ID=dbo.V_Pa yment_enrollment_end_dt_AIMS.Enrollment_ID
where dbo.Payment_Enrollment_AIMS.JC_ID = dbo.V_Payment_enrollment_end_dt_AIMS.JC_ID
and dbo.Payment_Enrollment_AIMS.[PROJ START DT] =dbo.V_Payment_enrollment_end_dt_AIMS.[PROJ START DT]
and dbo.Payment_Enrollment_AIMS.[Grant]=dbo.V_Payment_enrollment_end_dt_AIMS.[Grant]
and dbo.Payment_Enrollment_AIMS.TERM= dbo.V_Payment_enrollment_end_dt_AIMS.TERM
UPDATE dbo.Payment_Placement_AIMS
SET dbo.Payment_Placement_AIMS.[EMP END DT]= dbo.V_Payment_placement_end_dt_AIMS.DTE_END_EMPLR
FROM dbo.Payment_Placement_AIMS JOIN dbo.V_Payment_placement_end_dt_AIMS
ON dbo.Payment_Placement_AIMS.JC_ID = dbo.V_Payment_placement_end_dt_AIMS.JC_ID
where dbo.Payment_Placement_AIMS.[EMP START DT] =dbo.V_Payment_placement_end_dt_AIMS.[EMP START DT]
and dbo.Payment_Placement_AIMS.[Grant]=dbo.V_Payment_placement_end_dt_AIMS.CDE_PROJ
and dbo.Payment_Placement_AIMS.HOURS= dbo.V_Payment_placement_end_dt_AIMS.HOURS
and dbo.Payment_Placement_AIMS.WAGE=dbo.V_Payment_plac ement_end_dt_AIMS.WAGE
UPDATE dbo.Payment_Activity_AIMS
SET dbo.Payment_Activity_AIMS.[ACTV END DT]= dbo.V_Payment_activity_end_dt_AIMS.DTE_END_ACTV
FROM dbo.Payment_Activity_AIMS JOIN dbo.V_Payment_activity_end_dt_AIMS
ON dbo.Payment_Activity_AIMS.ACTIVITY_ID =dbo.V_Payment_activity_end_dt_AIMS.ACTIVITY_ID
where dbo.Payment_Activity_AIMS.JC_ID = dbo.V_Payment_activity_end_dt_AIMS.JC_ID
and dbo.Payment_Activity_AIMS.[ACTV START DT] = dbo.V_Payment_activity_end_dt_AIMS.[ACTV START DT]
and dbo.Payment_Activity_AIMS.[ACTIVITY CODE]= dbo.V_Payment_activity_end_dt_AIMS.[ACTIVITY CODE]
and dbo.Payment_Activity_AIMS.PROGRAM= dbo.V_Payment_activity_end_dt_AIMS.PROGRAM
UPDATE dbo.Payment_Activity_AIMS
SET dbo.Payment_Activity_AIMS.[ACTV END DT]= dbo.V_Payment_activity_end_dt_AIMS.DTE_END_ACTV
FROM dbo.Payment_Activity_AIMS JOIN dbo.V_Payment_activity_end_dt_AIMS
ON dbo.Payment_Activity_AIMS.ACTIVITY_ID =dbo.V_Payment_activity_end_dt_AIMS.ACTIVITY_ID
where dbo.Payment_Activity_AIMS.JC_ID = dbo.V_Payment_activity_end_dt_AIMS.JC_ID
and dbo.Payment_Activity_AIMS.[ACTV START DT] = dbo.V_Payment_activity_end_dt_AIMS.[ACTV START DT]
and dbo.Payment_Activity_AIMS.[ACTIVITY CODE]= dbo.V_Payment_activity_end_dt_AIMS.[ACTIVITY CODE]
and dbo.Payment_Activity_AIMS.PROGRAM= dbo.V_Payment_activity_end_dt_AIMS.PROGRAM
UPDATE dbo.Payment_Subproject_Retention_AIMS
SET dbo.Payment_Subproject_Retention_AIMS.[SUBPROJ END DT]= dbo.V_Payment_Subproject_Retention_end_dt_AIMS.DTE _END_PROJ_SUB,
dbo.Payment_Subproject_Retention_AIMS.PROJ_END_DAT E= dbo.V_Payment_Subproject_Retention_end_dt_AIMS.DTE _PROJ_END
FROM dbo.Payment_Subproject_Retention_AIMS JOIN dbo.V_Payment_Subproject_Retention_end_dt_AIMS
ON dbo.Payment_Subproject_Retention_AIMS.Subproject_R etention_ID =dbo.V_Payment_Subproject_Retention_end_dt_AIMS.Su bproject_Retention_ID
where dbo.Payment_Subproject_Retention_AIMS.JC_ID = dbo.V_Payment_Subproject_Retention_end_dt_AIMS.JC_ ID
and dbo.Payment_Subproject_Retention_AIMS.[SUBPROJ START DT] = dbo.V_Payment_Subproject_Retention_end_dt_AIMS.[SUBPROJ START DT]
and dbo.Payment_Subproject_Retention_AIMS.[SUB PROJECT]= dbo.V_Payment_Subproject_Retention_end_dt_AIMS.[SUB PROJECT]
and dbo.Payment_Subproject_Retention_AIMS.PROJ_START_D ATE= dbo.V_Payment_Subproject_Retention_end_dt_AIMS.DTE _PROJ_BEGIN
UPDATE dbo.Payment_Placement_Retention_AIMS
SET dbo.Payment_Placement_Retention_AIMS.[EMP END DT]= V_Payment_Placement_Retention_end_dt_AIMS.DTE_END_ EMPLR
FROM dbo.Payment_Placement_Retention_AIMS JOIN V_Payment_Placement_Retention_end_dt_AIMS
ON dbo.Payment_Placement_Retention_AIMS.Placement_Ret ention_ID =V_Payment_Placement_Retention_end_dt_AIMS.Placeme nt_Retention_ID
where dbo.Payment_Placement_Retention_AIMS.JC_ID = V_Payment_Placement_Retention_end_dt_AIMS.JC_ID
and dbo.Payment_Placement_Retention_AIMS.[Grant] = V_Payment_Placement_Retention_end_dt_AIMS.[Grant]
and dbo.Payment_Placement_Retention_AIMS.[EMP START DT]= V_Payment_Placement_Retention_end_dt_AIMS.[EMP START DT]
UPDATE dbo.Payment_Placement_Retention_AIMS
SET dbo.Payment_Placement_Retention_AIMS.PROJ_END_DATE = dbo.V_Payment_Placement_Retention_proj_end_dt_AIMS .DTE_PROJ_END
FROM dbo.Payment_Placement_Retention_AIMS JOIN dbo.V_Payment_Placement_Retention_proj_end_dt_AIMS
ON dbo.Payment_Placement_Retention_AIMS.Placement_Ret ention_ID =dbo.V_Payment_Placement_Retention_proj_end_dt_AIM S.Placement_Retention_ID
where dbo.Payment_Placement_Retention_AIMS.JC_ID = dbo.V_Payment_Placement_Retention_proj_end_dt_AIMS .JC_ID
and dbo.Payment_Placement_Retention_AIMS.[Grant] = dbo.V_Payment_Placement_Retention_proj_end_dt_AIMS .[Grant]
and dbo.Payment_Placement_Retention_AIMS.PROJ_START_DA TE= dbo.V_Payment_Placement_Retention_proj_end_dt_AIMS .PROJ_START_DATE
and dbo.Payment_Placement_Retention_AIMS.CDE_PROJ_TERM = dbo.V_Payment_Placement_Retention_proj_end_dt_AIMS .CDE_PROJ_TERM