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 SP

Author  Topic 

Pasi
Posting Yak Master

166 Posts

Posted - 2014-08-01 : 19:03:07
HI All,

I need help modifying my SP to check and see if there are duplicate "person_id" exist in NGweb_bulk_enrollment and prompt for " Person already exist" Can this be done with Prompt? Here is my code:

what this SP does it grabs the person id from the application currently logged in and adds it to bulk enrollment table.

I am not sure where/how to add the "Count(*) row or something like that to check for duplicates?

Thanks a lot!!



CREATE 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 ngweb_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,'red','color','red',@user_id,current_timestamp, @user_id,current_timestamp)


END

SET NOCOUNT off;





GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-01 : 19:25:02
Shouldn't you just have a unique index/constriant on person_id? Then put the INSERT into a TRY/CATCH.

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-08-01 : 19:33:04
Yes the person id is unique. I don't know how to construct the code to check for duplicates? or TRY/CATCH? I have not done it before.

Pasi.


quote:
Originally posted by tkizer

Shouldn't you just have a unique index/constriant on person_id? Then put the INSERT into a TRY/CATCH.

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-08-01 : 19:37:06
If it's unique, then the INSERT will throw an error when a duplicate is attempted. Catch that error in your application code. If you want to more gracefully handle it, then use TRY/CATCH. There are examples in BOL.

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

- Advertisement -