stored procedure nightmare-- Help!

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

stored procedure nightmare-- Help!

Author

Topic

odxsigma
Starting Member

1 Post

Posted - 2008-04-09 : 18:08:07

hi All

in short, what i'm trying to do is add another field called CompanyId ( uniqueidentifier) to the aspnet_Membership table and aspnet_Users table which comes in the classifieds starter kit,

Curently, when a usere registers, a UserId ( uniqueidentifier) is created and stored both the aspnet_Membership table and aspnet_Users table, i just want to duplicate this functionality and have CompanyID in the same tables in the same way.

Ive been messing around with the stored precedures for a while but i'm getting nowhere, if anyone could help, or even explain a little how the UserId is passed from one table to another, i'd be so greatful

I have added the two stored precedures bellow, first is called ALTER PROCEDURE dbo.aspnet_Membership_CreateUse

seccond is called ALTER PROCEDURE [dbo].aspnet_Users_CreateUser

thanks a lot
odxsigma

ALTER PROCEDURE dbo.aspnet_Membership_CreateUser
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@Password nvarchar(128),
@PasswordSalt nvarchar(128),
@Email nvarchar(256),
@PasswordQuestion nvarchar(256),
@PasswordAnswer nvarchar(128),
@IsApproved bit,
@CurrentTimeUtc datetime,
@CreateDate datetime = NULL,
@UniqueEmail int = 0,
@PasswordFormat int = 0,
@UserId uniqueidentifier OUTPUT
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL

DECLARE @NewUserId uniqueidentifier
SELECT @NewUserId = NULL

DECLARE @IsLockedOut bit
SET @IsLockedOut = 0

DECLARE @LastLockoutDate datetime
SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )

DECLARE @FailedPasswordAttemptCount int
SET @FailedPasswordAttemptCount = 0

DECLARE @FailedPasswordAttemptWindowStart datetime
SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )

DECLARE @FailedPasswordAnswerAttemptCount int
SET @FailedPasswordAnswerAttemptCount = 0

DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )

DECLARE @NewUserCreated bit
DECLARE @ReturnValue int
SET @ReturnValue = 0

DECLARE @ErrorCode int
SET @ErrorCode = 0

DECLARE @TranStarted bit
SET @TranStarted = 0

IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0

EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END

SET @CreateDate = @CurrentTimeUtc

SELECT @NewUserId = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName AND @ApplicationId = ApplicationId
IF ( @NewUserId IS NULL )
BEGIN
SET @NewUserId = @UserId
EXEC @ReturnValue = dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT
SET @NewUserCreated = 1
END
ELSE
BEGIN
SET @NewUserCreated = 0
IF( @NewUserId <> @UserId AND @UserId IS NOT NULL )
BEGIN
SET @ErrorCode = 6
GOTO Cleanup
END
END

IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END

IF( @ReturnValue = -1 )
BEGIN
SET @ErrorCode = 10
GOTO Cleanup
END

IF ( EXISTS ( SELECT UserId
FROM dbo.aspnet_Membership
WHERE @NewUserId = UserId ) )
BEGIN
SET @ErrorCode = 6
GOTO Cleanup
END

SET @UserId = @NewUserId

IF (@UniqueEmail = 1)
BEGIN
IF (EXISTS (SELECT *
FROM dbo.aspnet_Membership m WITH ( UPDLOCK, HOLDLOCK )
WHERE ApplicationId = @ApplicationId AND LoweredEmail = LOWER(@Email)))
BEGIN
SET @ErrorCode = 7
GOTO Cleanup
END
END

IF (@NewUserCreated = 0)
BEGIN
UPDATE dbo.aspnet_Users
SET LastActivityDate = @CreateDate
WHERE @UserId = UserId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
END

INSERT INTO dbo.aspnet_Membership
( ApplicationId,
UserId,
Password,
PasswordSalt,
Email,
LoweredEmail,
PasswordQuestion,
PasswordAnswer,
PasswordFormat,
IsApproved,
IsLockedOut,
CreateDate,
LastLoginDate,
LastPasswordChangedDate,
LastLockoutDate,
FailedPasswordAttemptCount,
FailedPasswordAttemptWindowStart,
FailedPasswordAnswerAttemptCount,
FailedPasswordAnswerAttemptWindowStart )
VALUES ( @ApplicationId,
@UserId,
@Password,
@PasswordSalt,
@Email,
LOWER(@Email),
@PasswordQuestion,
@PasswordAnswer,
@PasswordFormat,
@IsApproved,
@IsLockedOut,
@CreateDate,
@CreateDate,
@CreateDate,
@LastLockoutDate,
@FailedPasswordAttemptCount,
@FailedPasswordAttemptWindowStart,
@FailedPasswordAnswerAttemptCount,
@FailedPasswordAnswerAttemptWindowStart )

IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END

IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END

RETURN 0

Cleanup:

IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END

RETURN @ErrorCode

END

ALTER PROCEDURE [dbo].aspnet_Users_CreateUser
@ApplicationId uniqueidentifier,
@UserName nvarchar(256),
@IsUserAnonymous bit,
@LastActivityDate DATETIME,
@UserId uniqueidentifier OUTPUT,
@CompanyId int OUTPUT
AS
BEGIN
IF( @UserId IS NULL )
SELECT @UserId = NEWID()
ELSE
BEGIN
IF( EXISTS( SELECT UserId FROM dbo.aspnet_Users
WHERE @UserId = UserId ) )
RETURN -1
END

INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
VALUES (@ApplicationId, @UserId, @UserName, LOWER(@UserName), @IsUserAnonymous, @LastActivityDate)

RETURN 0
END

thanks a lot

odxsigma

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-04-10 : 09:47:26

First off, I'd ask why you want this in both tables? I could understand having it in the user table but not the membership table. Secondly, where is the company ID coming from? Based on your comment: "Curently, when a usere registers, a UserId ( uniqueidentifier) is created and stored both the aspnet_Membership table and aspnet_Users table, i just want to duplicate this functionality and have CompanyID in the same tables in the same way.", it's also going to be a unique identifier? Do you have a company table that will contain this identifier? The stored procs seem impressive but what is the ultimate goal you're trying to acheive with the new field (it's currently only an output parm in aspnet_Users_CreateUser)? Please post table structures as well.

Terry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources