Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Development Tools
 ASP.NET
 How to create stored procedure in SQL server 2005

Author  Topic 

svgeorge
Starting Member

23 Posts
Posted - 2007-10-17 : 14:10:42
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_Payment_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_placement_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_DATE= 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_Retention_ID =dbo.V_Payment_Subproject_Retention_end_dt_AIMS.Subproject_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_DATE= 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_Retention_ID =V_Payment_Placement_Retention_end_dt_AIMS.Placement_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_Retention_ID =dbo.V_Payment_Placement_Retention_proj_end_dt_AIMS.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_DATE= 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
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site