Could someone post the code for sp_Accounts_DeleteUser it's not in the backup.

thanks
Sunit
sjoshi@i...
sunitjoshi@n...

> Could someone post the code for sp_Accounts_DeleteUser it's not in the backup.

> thanks
S> unit
s> joshi@i...
s> unitjoshi@n...

Also cannot find sp_Accounts_GetPermissionDetails

thanks
Sunit

Yes the sp_Accounts_DeleteUser is called for in the book's code but not
included in the downloaded SQL file.  I haven't studied that particular
module enough to help suggest a reasonable solution.  Anyone associated with
the book actually reading this list?  --Graham

>> Could someone post the code for sp_Accounts_DeleteUser it's not in the
backup.

>> thanks
S>> unit
s>> joshi@i...
s>> unitjoshi@n...

>Also cannot find sp_Accounts_GetPermissionDetails

Graham Dobson wrote:
> Yes the sp_Accounts_DeleteUser is called for in the book's code but
> not included in the downloaded SQL file.  I haven't studied that

I'd suggest this is one of the beauties of the book.  It gives you a
chance to make sure that you understand what is happening.  Look at the
source for the SP's and write your own to fill the gap.

When you've done that you know that you understand it!

Mike Gale, Decision Engineering (NZ) Ltd.

Curiously enough, this procedure isn't even named in the book. I'm assuming
it would have to clear any entries in the Accounts_UserRoles table and then
the user from the Accounts_Users table,  or use a trigger, which seems to be
the preferred method of the authors, although no such trigger exists for
Accounts_UserRoles in the SQL script download.  I have noticed that while
most of the transaction based procedures in this book are quite instructive.
sp_MLists_UpdateSubscription seems flawed to me.

CREATE PROCEDURE sp_MLists_UpdateSubscription
@SubscriptionID	int,
@FirstName		varchar(50),
@LastName		varchar(50),
@Email		varchar(50),
@Active		bit
AS

BEGIN TRANSACTION UpdateSubscription

DECLARE @UserID int

-- get the ID of the user, which match an user in the MLists_Users table
SET @UserID = (SELECT UserID FROM MLists_Subscriptions WHERE SubscriptionID
= @SubscriptionID)

-- update the user's info
UPDATE MLists_Users
	SET FirstName = @FirstName, LastName = @LastName, Email = @Email
	WHERE UserID = @UserID

-- update the subscription's active state
UPDATE MLists_Subscriptions
	SET Active = @Active
	WHERE SubscriptionID = @SubscriptionID

IF @@ERROR > 0
	BEGIN
	RAISERROR ('Update of Subscription failed', 16, 1)
	ROLLBACK TRANSACTION UpdateSubscription
	RETURN 99
	END

COMMIT TRANSACTION UpdateSubscription

I suppose this is a remote (if not impossible eventuality) but if the first
update succeeds and the second fails, the first update would not be rolled
back.  Many people don't seem to understand this.  There are two possible
safeguards.  Add an error check after the first routine or (and this is the
lesser of the two solutions) use the SET_XACT_ABORT option.  I believe this
is a common misconception regarding transactions and is explained
exceedingly well in Inside SQL Server.  You are essentially right though,
this book (ASP.NET Website programming) is so much more interesting in it's
flawed majesty, than many other lesser books for all their perfection.

>>Graham Dobson wrote:
> >Yes the sp_Accounts_DeleteUser is called for in the book's code but
> >not included in the downloaded SQL file.  I haven't studied that

>I'd suggest this is one of the beauties of the book.  It gives you a
>chance to make sure that you understand what is happening.  Look at the
>source for the SP's and write your own to fill the gap.

>When you've done that you know that you understand it!

>Mike Gale, Decision Engineering (NZ) Ltd.

>sp_MLists_UpdateSubscription seems flawed to me.

Actually It's my thinking that's a bit flawed in that last post.  I have a
question, if the first update fails does the error count cause the entire
transaction to be rolled back?  I believe the transaction should check for
errors after each update, furthermore it should also check @@ROWCOUNT to
make sure there was an update.  No records affected is not an error!  All of
this is discussed in Batches, Transactions, Stored Procedures and Triggers
in Inside SQL Server.  So while my statements regarding
sp_UpdateSubscription from the last post were random to say the least, I do
feel that this sproc is unbalanced and somehow incorrect.

In this case, since this is not a nested transaction, I do think that we would need
SET XACT_ABORT ON 
Begin Transaction
......


Sunit

> >sp_MLists_UpdateSubscription seems flawed to me.

Actually It's my thinking that's a bit flawed in that last post.  I have a
question, if the first update fails does the error count cause the entire
transaction to be rolled back?  I believe the transaction should check for
errors after each update, furthermore it should also check @@ROWCOUNT to
make sure there was an update.  No records affected is not an error!  All of
this is discussed in Batches, Transactions, Stored Procedures and Triggers
in Inside SQL Server.  So while my statements regarding
sp_UpdateSubscription from the last post were random to say the least, I do
feel that this sproc is unbalanced and somehow incorrect.