|
|
Posted by Plamen Ratchev on 06/05/07 15:36
I will try to sketch here the answer to both questions:
1). To detect that the confirmation code exists, you can check the number of
rows affected by the insert (using @@rowcount), and then return that value
to the client using an output parameter. If the number of rows is 1 (I
assume you have either a primary key or UNIQUE constraint on the
confirmation code column so duplicates are not possible), then you know you
had a code match, if 0 then there was no match. Here is an abbreviated code
of the SP:
CREATE PROCEDURE ConfirmUserRegistration
@confirmation_cd NVARCHAR(50),
@numrows INT OUTPUT
AS
-- ....
BEGIN TRAN
INSERT INTO Users
(egdate,
pass,
-- ...
firstName)
SELECT regdate,
pass,
-- ...
firstName
FROM TempRegistration
WHERE confirm = @confirmation_cd;
SET @numrows = @@rowcount;
DELETE FROM TempRegistration
WHERE confirm = @confirmation_cd;
COMMIT TRAN;
Note that you can directly perform the DELETE without checking the result of
the INSERT, because if there is no match then there will be no rows deleted.
If you want you can have an IF @numrows > 0 before executing the DELETE
statement to run it only when there is a match.
2). On your client side, you have to define the output parameter and then
check the results, abbreviated code here:
'... connection, command and first parameter initialization go here
' now add the output parameter
parameter = command.Parameters.Add( _
"@numrows",
SqlDbType.Int)
parameter.Direction = ParameterDirection.Output
'... open the connection and execute command go here
' retrieve the output value
If (command.Parameters("@numrows").Value = 1) Then
' we have a match and confirmation is complete
Else
' confirmation code is invalid - show alert
End If
HTH,
Plamen Ratchev
http://www.SQLStudio.com
Navigation:
[Reply to this message]
|