Update 2 tables in one sp - 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

Update 2 tables in one sp

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-10-05 : 11:11:31

I want to update 2 tables in one sp as the parameters are the same. Here's my sp and I don't get any syntax errors in Ent manager, but the error 'Incorrect syntax near 'spRB_CancelFacMenuBooking' when I run it.

CREATE PROCEDURE spRB_CancelFacMenuBooking

@strBookingNo integer,
@strCancelled bit,
@strCancelledDate datetime,
@strCancelledBy nvarchar(100)

AS
BEGIN
UPDATE tblRB_FacilitiesBookings SET
FB_Cancelled =@strCancelled,
FB_CancelledDate=@strCancelledDate,
FB_CancelledBy=@strCancelledBy
WHERE FB_BookingNo = @strBookingNo
END

BEGIN
UPDATE tblRB_MenuBookings SET
MB_Cancelled =@strCancelled,
MB_CancelledDate=@strCancelledDate,
MB_CancelledBy=@strCancelledBy
WHERE MB_BookingNo = @strBookingNo
END
GO

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-05 : 11:23:32

you forgot the "( )"

CREATE PROCEDURE spRB_CancelFacMenuBooking
(
@strBookingNo integer,
@strCancelled bit,
@strCancelledDate datetime,
@strCancelledBy nvarchar(100)
)
AS

Duane.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-10-05 : 11:27:09

I still get the same error.....

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-10-05 : 11:31:06

[code]CREATE PROCEDURE spRB_CancelFacMenuBooking

@strBookingNo integer,
@strCancelled bit,
@strCancelledDate datetime,
@strCancelledBy nvarchar(100)

AS
BEGIN
UPDATE tblRB_FacilitiesBookings SET
FB_Cancelled =@strCancelled,
FB_CancelledDate=@strCancelledDate,
FB_CancelledBy=@strCancelledBy
WHERE FB_BookingNo = @strBookingNo

UPDATE tblRB_MenuBookings SET
MB_Cancelled =@strCancelled,
MB_CancelledDate=@strCancelledDate,
MB_CancelledBy=@strCancelledBy
WHERE MB_BookingNo = @strBookingNo
END
GO[/code]However, It would bu usefull if you add error handling.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-10-05 : 11:35:06

Thanks for your help. How would I add error handling ? I am going to put Try etc around my sp call in my asp.net code.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-10-05 : 11:40:37

You can also add transactions. if not then at least after each update add:

if @@error <> 0 return <integer error code>

also add at the end of proc:

return 0

and check return value in your .net code.

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-05 : 12:59:07

Best thing I can say is to NOT bo this in Enterprise Mangler. Do this in Query Analyzer.

And try and search the site

http://www.sqlteam.com/item.asp?ItemID=2463

Lots of good stuff to read

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-10-06 : 04:00:28

I am still getting the same error when I run the sp. I will create two instead.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-06 : 04:29:34

how are you calling the sp?

--------------------
keeping it simple...

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-10-06 : 04:31:09

I've sorted it now - typo I'm afraid...