Help with writing a Stored Procedure

Hi there,
I would be really grateful with some assistance with this, i am 100% new to
SQL & Stored Procedures. I'm building an ASP web application, i need to be
able to update two fields in two related tables & could really do with some
assistance. The tables are tbCompany and tbemployee. tbemployee is related t
o
tbCompany through a field in tbemployee named CompanyId (int). The fields
that i need to update are CompanySuspendedAccount in tbCompany
EmpCompAccoutSuspended in tbemployee. Needless to say there could be multipl
e
records in tbemployee that are related to the one record in tbCompany.
Ideally i will need to write two stored procedures, the first that updates
all records to yes and the second to no.
I would be really grateful for some assitance with this - Many thanks
GarethAssuming some thing that you didn=B4t posted a SP could bve something
like this for you:
CREATE PROCEDURE UPDSuspendCode
(
@.CompanyId INT,
@.Suspend BIT
)
AS
BEGIN
DECLARE @.Error INT
BEGIN TRANSACTION
UPDATE tbCompany
SET CompanySuspendedAccount =3D @.Suspend
Where CompanyId =3D @.CompanyId
SET @.ERROR =3D @.@.Error
UPDATE tbemployee
SET EmpCompAccoutSuspended =3D @.Suspend
Where CompanyId =3D @.CompanyId
SET @.ERROR =3D @.Error + @.@.Error
IF @.Error > 0
BEGIN
RAISERROR('And Error has occured during updating the suspend
status',16,1)
ROLLBACK
END
ELSE
COMMIT
END
HTH, Jens Suessmeyer.|||Thanks Jene, it looks good to me, presumably here i am setting the value to
Suspend? So i could use @.yes BIT for the suspending of the accounts and @.no
BIT to unsuspend. Both the fields CompanySuspendedAccount &
EmpCompAccoutSuspended are nvarchar so presumably it would need to be as
above unless i change the database? Thanks for your help
Gareth
"Jens" wrote:

> Assuming some thing that you didn′t posted a SP could bve something
> like this for you:
> CREATE PROCEDURE UPDSuspendCode
> (
> @.CompanyId INT,
> @.Suspend BIT
> )
> AS
> BEGIN
> DECLARE @.Error INT
> BEGIN TRANSACTION
>
> UPDATE tbCompany
> SET CompanySuspendedAccount = @.Suspend
> Where CompanyId = @.CompanyId
> SET @.ERROR = @.@.Error
>
> UPDATE tbemployee
> SET EmpCompAccoutSuspended = @.Suspend
> Where CompanyId = @.CompanyId
> SET @.ERROR = @.Error + @.@.Error
> IF @.Error > 0
> BEGIN
> RAISERROR('And Error has occured during updating the suspend
> status',16,1)
> ROLLBACK
> END
> ELSE
> COMMIT
> END
>
> HTH, Jens Suessmeyer.
>|||"@.yes BIT " Thats what my @.Suspend is for, you don=B4t need a @.Suspende
AND @.Unsuspense because the twi BITs can never evaluate to true (can be
either switch on AND off, right ).
You don=B4t have to change the database because BIT a Integer data type
with the constraint of being 1, 0, or NULL, so implicit conversion will
be used.
DECLARE @.Suspense BIT
DECLARE @.SomeColumn NVARCHAR(200)
SET @.Suspense =3D 1
SET @.SomeColumn =3D @.Suspense
Print @.SomeColumn
HTH, jens Suessmeyer.