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

[Back to original message]