BeginningWebDesign
asked on
Check SQL Code
Hi
I have the following code, just really want to know whether it the most efficient way.
ALTER PROCEDURE [dbo].[Activate_Account]
@MemberID VarChar(50) = '4b95187c-00ee-453b-9daf-4 56c3952da7 7'
AS
DECLARE @CurrentTime DateTime
SET @CurrentTime = GetDate()
DECLARE @MemberAccountCreated DateTime
DECLARE @TimeDiff INT
DECLARE @ReturnCode INT
SET @ReturnCode = 0
BEGIN
SET NOCOUNT ON;
SELECT @MemberAccountCreated = dbo.Membership.MemberAccou ntCreated
FROM dbo.Membership
WHERE MemberUserID = @MemberID
SELECT @TimeDiff = CAST(a - b AS numeric(18,2))
FROM (
SELECT
CAST(@CurrentTime AS datetime) AS a,
CAST(@MemberAccountCreated AS datetime) AS b
)AS TimeDiff
UPDATE dbo.Membership
SET MemberActivateAccount = 1
WHERE MemberUserID = @MemberID AND @TimeDiff < 2
IF(@TimeDiff > 2)
BEGIN
SET @ReturnCode = 1
END
ELSE IF(@@RowCount > 0)
BEGIN
SET @ReturnCode = 2
END
ELSE
BEGIN
SET @ReturnCode = 3
END
SELECT @ReturnCode AS ReturnCode
END
I have the following code, just really want to know whether it the most efficient way.
ALTER PROCEDURE [dbo].[Activate_Account]
@MemberID VarChar(50) = '4b95187c-00ee-453b-9daf-4
AS
DECLARE @CurrentTime DateTime
SET @CurrentTime = GetDate()
DECLARE @MemberAccountCreated DateTime
DECLARE @TimeDiff INT
DECLARE @ReturnCode INT
SET @ReturnCode = 0
BEGIN
SET NOCOUNT ON;
SELECT @MemberAccountCreated = dbo.Membership.MemberAccou
FROM dbo.Membership
WHERE MemberUserID = @MemberID
SELECT @TimeDiff = CAST(a - b AS numeric(18,2))
FROM (
SELECT
CAST(@CurrentTime AS datetime) AS a,
CAST(@MemberAccountCreated
)AS TimeDiff
UPDATE dbo.Membership
SET MemberActivateAccount = 1
WHERE MemberUserID = @MemberID AND @TimeDiff < 2
IF(@TimeDiff > 2)
BEGIN
SET @ReturnCode = 1
END
ELSE IF(@@RowCount > 0)
BEGIN
SET @ReturnCode = 2
END
ELSE
BEGIN
SET @ReturnCode = 3
END
SELECT @ReturnCode AS ReturnCode
END
ASKER
Hi BrandonGalderisi:
Thanks, the code returns 2(success) even when the date is greater than 2, it should return 1
George
Thanks, the code returns 2(success) even when the date is greater than 2, it should return 1
George
OK, we know that memberaccountcreated is meant to be a datetime...
because of : SELECT @MemberAccountCreated = dbo.Membership.MemberAccou ntCreated FROM dbo.Membership...
So, we also know that CurrentTime is similarly a legitimate datetime, so not sure whay the "CAST" is required, so we can achieve a lot in one statement...
because of : SELECT @MemberAccountCreated = dbo.Membership.MemberAccou
So, we also know that CurrentTime is similarly a legitimate datetime, so not sure whay the "CAST" is required, so we can achieve a lot in one statement...
UPDATE Membership SET MemberActivateAccount = 1
WHERE MemberUserID = @MemberID
AND datediff(dd,MemberAccountCreated, getdate()) < 2
IF(@@RowCount > 0)
SET @ReturnCode = 2
ELSE
SET @ReturnCode = 1
-- It would appear that returncode = 1 is an un-updateable query where data exists, just not within 2 days.
-- Similarly, returncode of 3 means similar things, but could also mean that member does not exist. Do you really need that three part flag ?
ASKER
Hi mark_wills:
ReturnCode 1 = Successfull update, MemberUserID exists and account is activated within 2 days.
ReturnCode 2 = MemberUserID exists, but new member has not activated account within the 2 day limit so no update has taken place.
ReturnCode 3 = No update has taken place not really bothered why, just inform new member that cannot activate account
Hope this helps
George
ReturnCode 1 = Successfull update, MemberUserID exists and account is activated within 2 days.
ReturnCode 2 = MemberUserID exists, but new member has not activated account within the 2 day limit so no update has taken place.
ReturnCode 3 = No update has taken place not really bothered why, just inform new member that cannot activate account
Hope this helps
George
ASKER
Sorry my mistake wrong way round, should read
ReturnCode 1 = MemberUserID exists, but new member has not activated account within the 2 day limit so no update has taken place.
ReturnCode 2 = Successfull update, MemberUserID exists and account is activated within 2 days.
ReturnCode 3 = No update has taken place not really bothered why, just inform new member that cannot activate account
George
ReturnCode 1 = MemberUserID exists, but new member has not activated account within the 2 day limit so no update has taken place.
ReturnCode 2 = Successfull update, MemberUserID exists and account is activated within 2 days.
ReturnCode 3 = No update has taken place not really bothered why, just inform new member that cannot activate account
George
So, 1 and 3 are essentially the same, except that 3 no longer has an opportunity to activate and really, the only time that is different to 1 is where memberid does not exist - so really - what is the difference ?
ASKER
Hi Mark
This is what i should have wrote, 3 will only be returned if an error occured, 2 if update success and 1 if update failed due to date diff been more than 2 days
IF(@@RowCount > 0)
SET @ReturnCode = 2
ELSE
SET @ReturnCode = 1
SELECT @Error = @@Error
IF @Error != 0
BEGIN
SET @ReturnCode = 3
END
SELECT @ReturnCode AS ReturnCode
END
Questions is, will @Error ever run as i have tried a few examples and all have failed getting @ReturnCode to = 3
George
This is what i should have wrote, 3 will only be returned if an error occured, 2 if update success and 1 if update failed due to date diff been more than 2 days
IF(@@RowCount > 0)
SET @ReturnCode = 2
ELSE
SET @ReturnCode = 1
SELECT @Error = @@Error
IF @Error != 0
BEGIN
SET @ReturnCode = 3
END
SELECT @ReturnCode AS ReturnCode
END
Questions is, will @Error ever run as i have tried a few examples and all have failed getting @ReturnCode to = 3
George
I think a return code of 3 will only ever be legitimate if @MemberID does not exist (looking at the very original) or, the @MemberID is held under lock and the update has to time out / fail...
So, it is possible for a legitimate error to happen... but outcome / followup action is more like returncode =1 ( meaning please update your membership before it is too late )...
Now if @memberid does not exist, or more than 'nn' days. Could check for 'cleanup' actions... maybe a returncode of 4 ?
Your call, and can very simply add error checking, the reason why you are not getting errors is possibly because how it is being checked. Need to capture both at the same time (in yours the @@error is responding to the IF statement).
So, it is possible for a legitimate error to happen... but outcome / followup action is more like returncode =1 ( meaning please update your membership before it is too late )...
Now if @memberid does not exist, or more than 'nn' days. Could check for 'cleanup' actions... maybe a returncode of 4 ?
Your call, and can very simply add error checking, the reason why you are not getting errors is possibly because how it is being checked. Need to capture both at the same time (in yours the @@error is responding to the IF statement).
DECLARE @ErrorVar INT,
@RowCountVar INT;
UPDATE Membership SET MemberActivateAccount = 1
WHERE MemberUserID = @MemberID
AND datediff(dd,MemberAccountCreated, getdate()) < 2
SELECT @ErrorVar = @@ERROR, @RowCountVar = @@ROWCOUNT;
IF(@RowCountVar > 0)
SET @ReturnCode = 2
ELSE
IF (@errorVar > 0
SET @ReturnCode = 3
ELSE
SET @ReturnCode = 1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window