Updating multiple columns - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Updating multiple columns

Author

Topic

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2007-04-02 : 12:55:46

Hi guys.........i'm tryign to update 2 tables in one stored procedure. However i'm getting errors. heres the code i have:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[snow_ors_additionalInfoUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[snow_ors_additionalInfoUpdate]
GO
CREATE   PROCEDURE dbo.snow_ors_additionalInfoUpdate
		@Reference int,
	 	@CanTravel int,
		@SEEmployee varchar(100),
		@SE_EMPLOYEE_FROM datetime,
		@SE_EMPLOYEE_TO datetime,
		@WorkHours Varchar(100),
		@DrivingLicence varchar(100),
		@CriminalConvictions bit,
		@CriminalConvictionsDetails1 text,
		@CriminalConvictionsDate1 datetime,
		@CriminalConvictionsDetails2 text,
		@CriminalConvictionsDate2 datetime,
		@CriminalConvictionsDetails3 text,
		@CriminalConvictionsDate3 datetime,
		@VacancyMonitoring Varchar(100),
		@VacancyMonitoringDetails Varchar(50),
		
AS
	UPDATE Account
	SET 	CanTravel= @CanTravel,
		SEEmployee = @SEEmployee,
		WorkHours= @WorkHours,
		DrivingLicence = @DrivingLicence,
		CriminalConvictions = @CriminalConvictions,
		CriminalConvictionsDetails1 = @CriminalConvictionsDetails1,
		CriminalConvictionsDate1 = @CriminalConvictionsDate1,
		CriminalConvictionsDetails2 = @CriminalConvictionsDetails2,
		CriminalConvictionsDate2 = @CriminalConvictionsDate2,
		CriminalConvictionsDetails3 = @CriminalConvictionsDetails3,
		CriminalConvictionsDate3 = @CriminalConvictionsDate3
		WHERE Account.Reference = @Reference
	
	UPDATE Application
	SET	VacancyMonitoring = @VacancyMonitoring,
		VacancyMonitoringDetails = @VacancyMonitoringDetails,
		WHERE Application.Reference = @Reference
		
GO

and here are the errors i'm getting:

Server: Msg 156, Level 15, State 1, Procedure snow_ors_additionalInfoUpdate, Line 19
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Procedure snow_ors_additionalInfoUpdate, Line 37
Incorrect syntax near the keyword 'WHERE'.

thanks again guys!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-04-02 : 13:00:45

You have a couple of stray commas

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[snow_ors_additionalInfoUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[snow_ors_additionalInfoUpdate]
GO
CREATE   PROCEDURE dbo.snow_ors_additionalInfoUpdate
		@Reference int,
	 	@CanTravel int,
		@SEEmployee varchar(100),
		@SE_EMPLOYEE_FROM datetime,
		@SE_EMPLOYEE_TO datetime,
		@WorkHours Varchar(100),
		@DrivingLicence varchar(100),
		@CriminalConvictions bit,
		@CriminalConvictionsDetails1 text,
		@CriminalConvictionsDate1 datetime,
		@CriminalConvictionsDetails2 text,
		@CriminalConvictionsDate2 datetime,
		@CriminalConvictionsDetails3 text,
		@CriminalConvictionsDate3 datetime,
		@VacancyMonitoring Varchar(100),
		@VacancyMonitoringDetails Varchar(50),
		
AS
	UPDATE Account
	SET 	CanTravel= @CanTravel,
		SEEmployee = @SEEmployee,
		WorkHours= @WorkHours,
		DrivingLicence = @DrivingLicence,
		CriminalConvictions = @CriminalConvictions,
		CriminalConvictionsDetails1 = @CriminalConvictionsDetails1,
		CriminalConvictionsDate1 = @CriminalConvictionsDate1,
		CriminalConvictionsDetails2 = @CriminalConvictionsDetails2,
		CriminalConvictionsDate2 = @CriminalConvictionsDate2,
		CriminalConvictionsDetails3 = @CriminalConvictionsDetails3,
		CriminalConvictionsDate3 = @CriminalConvictionsDate3
		WHERE Account.Reference = @Reference
	
	UPDATE Application
	SET	VacancyMonitoring = @VacancyMonitoring,
		VacancyMonitoringDetails = @VacancyMonitoringDetails,
		WHERE Application.Reference = @Reference
		
GO

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2007-04-02 : 13:03:48

ahhhh, thanks snSQL :D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources