Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need help with creating a alert msg

Author  Topic 

Pasi
Posting Yak Master

166 Posts

Posted - 2014-09-29 : 13:25:50
Hi,

I have an SP that enrolls patients and inserts their user/pass/ect.. into "bulk enrollment"table. I need a way to check/embed if the person already enrolled and exist in this table give a msg saying "person already exist in db, how can I do this?
Thanks.

NOte: the SP runs from my application.

Here is my sp. I have an error msg at the end but its not working and I am not sure if it is the right one?

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



tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-29 : 13:32:13
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/
Go to Top of Page

Pasi
Posting Yak Master

166 Posts

Posted - 2014-09-29 : 13:54:28
Thanks! I have never used try/catch how do I construct in within the code?
Pai.

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/

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-29 : 14:03:29
quote:
Originally posted by Pasi

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


Check the documentation for it: http://msdn.microsoft.com/en-us/library/ms175976.aspx

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -