|
|
Posted by Plamen Ratchev on 06/05/07 03:15
Ok, now it is more clear what you are trying to do... :)
Yes, stored procedure is best here, as it can reuse previously cached
execution plan. Perhaps something like this:
CREATE PROCEDURE ConfirmUserRegistration
@confirmation_cd NVARCHAR(50)
AS
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRAN
INSERT INTO Users
(egdate,
pass,
role,
squestion,
sanswer,
zcode,
altemail,
email,
bdaymonth,
bdayday,
bdayyear,
gender,
sitename,
city,
state,
country,
lastName,
firstName)
SELECT regdate,
pass,
role,
squestion,
sanswer,
zcode,
altemail,
email,
bdaymonth,
bdayday,
bdayyear,
gender,
sitename,
city,
state,
country,
lastName,
firstName
FROM TempRegistration
WHERE confirm = @confirmation_cd;
COMMIT TRAN;
END TRY
BEGIN CATCH
IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRAN;
END
ELSE IF (XACT_STATE()) = 1
BEGIN
COMMIT TRAN;
END
DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorSeverity INT,
@ErrorState INT,
@ErrorNumber INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200),
@ErrMessage NVARCHAR(4000);
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorNumber = ERROR_NUMBER(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = COALESCE(ERROR_PROCEDURE(), '-');
SET @ErrMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, '
+
N'Message: '+ ERROR_MESSAGE();
RAISERROR(
@ErrMessage,
@ErrorSeverity,
1,
@ErrorNumber,
@ErrorSeverity,
@ErrorState,
@ErrorProcedure,
@ErrorLine
);
END CATCH;
GO
Then in your code call the SP like this (I just typed here, please check for
syntax, I've been using more C# lately and could be missing something):
Using connection As New
SqlConnection(ConfigurationManager.ConnectionStrings("myConnStr").ConnectionString)
Try
Dim command As SqlCommand = New SqlCommand( _
"ConfirmUserRegistration",
connection)
command.CommandType = CommandType.StoredProcedure
Dim parameter As SqlParameter = command.Parameters.Add( _
"@confirmation_cd ",
SqlDbType.NVarChar, _
50)
parameter.Value = "8c37a0737eegd64532rgdf56g5ec3f75aab5d9e7a712077"
command.Connection.Open()
command.ExecuteNonQuery()
Catch exSQL As SqlException
' Log and show error
Catch exGen As Exception
' Log and show error
End Try
End Using
HTH,
Plamen Ratchev
http://www.SQLStudio.com
Navigation:
[Reply to this message]
|