quote:

---

Alter PROCEDURE [dbo].[AutoEnroll] @person_id varchar(36), @user_id int
AS
DECLARE @practice_id char(4)
DECLARE @user_name varchar(50)
DECLARE @psswrd varchar(10)
DECLARE @enc_id uniqueidentifier

SET NOCOUNT ON

SELECT TOP 1 @practice_id = pe.practice_id
FROM patient_encounter pe
INNER JOIN patient pt ON pt.person_id = pe.person_id AND pt.practice_id = pe.practice_id
INNER JOIN person p ON p.person_id = pe.person_id
WHERE pe.person_id = @person_id
AND p.email_address <> ''
ORDER BY pe.enc_timestamp DESC

-- Build the user name and password
SELECT @user_name = RTRIM(LTRIM(last_name)) + RTRIM(LTRIM(LEFT(first_name, 1))) + RTRIM(LTRIM(CONVERT(varchar, person_nbr)))
, @psswrd = date_of_birth
FROM person WHERE person_id = @person_id


begin
INSERT INTO bulk_enrollments(row_id, person_id,practice_id, user_name,password,security_answer,forgot_password_question,forgot_password_answer,created_by,create_timestamp,modified_by,modified_timestamp)
Values (newid(),@person_id,'0001',@user_name, @psswrd,'brown','color','brown',@user_id,current_timestamp, @user_id,current_timestamp)

--IF @@ERROR = 1
--RAISERROR ('Duplicate patient !!!', 10, 10)
--print 'duplicates !!!'

END

SET NOCOUNT off;

GO

---

    SELECT  @ErrSeverity = ERROR_SEVERITY(),
            @ErrState = ERROR_STATE(),
            @ErrMsg = @ErrMsg + '. ' + ERROR_MESSAGE() + '. Error in line ' + CAST(ERROR_LINE() AS varchar(1000))

    RAISERROR(@ErrMsg, @ErrSeverity, @ErrState)

DECLARE @ErrMsg varchar(3000) = ''
       ,@ErrSeverity int
       ,@ErrState int

quote:

---

Originally posted by tkizer

Yours should say IF @@ERROR <> 0. @@ERROR will contain the error number. If no error, then it'll be zero.

But use TRY/CATCH instead and then in the CATCH section do something like this:

    SELECT  @ErrSeverity = ERROR_SEVERITY(),
            @ErrState = ERROR_STATE(),
            @ErrMsg = @ErrMsg + '. ' + ERROR_MESSAGE() + '. Error in line ' + CAST(ERROR_LINE() AS varchar(1000))

    RAISERROR(@ErrMsg, @ErrSeverity, @ErrState)

Here's the declares:

DECLARE @ErrMsg varchar(3000) = ''
       ,@ErrSeverity int
       ,@ErrState int

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

---

quote:

---

Originally posted by Pasi

Thanks! I have never used try/catch how do I construct in within the code?

---