SQL Check if Data already Used, if NOT Update

Question

Hey All, been on the forums looking for an answer for about a month and can't get it to run. Using SQL Sever Management Studio 2008 R2.

I have a table called 'applicants' with columns userid and SSN, userid being the primary key. My webform in visual studio allows admins to change the users SSN.

*HERE'S MY ISSUE* The change needs to check and see if the SSN is already in use. If it is, return some type of error stating that the desired SSN is in use and NOT update the user. If the SSN is not in use, then update the current userids SSN.

I have been trying with IF EXISTS/NOT EXISTS to no avail. I am fairly new to this so any help is appreciated.

Answer 1

Let me add that currently I can update SSN without a hitch. I just need the statement/code that will check if it is in use, before executing what I have. Currently I can update an SSN that is in use, resulting in two users having the same SSN.

Answer 2

Hi flamebro1287,

It is a sql related issue, I will move it to "Transact-SQL" forum for a better support.

Thank you for your understanding!

---

Lucy Liu [MSFT]
MSDN Community Support | Feedback to us

Answer 3

Do not you have an UNIQUE constraint on SSN column?

IF NOT EXISTS (SELECT * FROM tbl WHERE SSN=@SSN)

BEGIN

----Enter your logic here

END

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 4

The PK is the userID that is auto-generated from the form. SSN does not have a unique constraint and I am unable to make changes to the table itself.

Answer 5

This worked great- Thanks! I am using the following.

IF NOT EXISTS (SELECT * FROM IPS_APPLICANT_PROFILE WHERE SSN=@SSN)

BEGIN
UPDATE IPS_APPLICANT_PROFILE
SET 
SSN = @SSN
FROM IPS_APPLICANT_PROFILE
WHERE CJTC_ID = @CJTC_ID
END
ELSE

My hangup now is this: If it is successful send the user to a 'message successful' page and if it is unsuccessful, show some type of error on the webform. i.e. lit_errmsg.

Answer 6

Take a look at RAISERROR function in BOL in order to raise an error message.

Also I suggest to read this blog post because this pattern is a bit dangerous in multi-user environment

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2010/01/12/t-sql-tuesday-002-patterns-that-do-not-work-as-expected.aspx 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 7

Not sure if you are already, but try putting this in a stored procedure, and then just use an output parameter for your message:

Create Procedure dbo.prcUpdateSSN (
	@SSN				varchar(11)
	,@CJTC_ID			int
	,@ReturnMessage		varchar(100) OUTPUT
	)
AS

IF NOT EXISTS (SELECT * FROM IPS_APPLICANT_PROFILE WHERE SSN=@SSN)
BEGIN
	UPDATE	IPS_APPLICANT_PROFILE
	SET		SSN = @SSN
	FROM	IPS_APPLICANT_PROFILE
	WHERE	CJTC_ID = @CJTC_ID
	
	Set @ReturnMessage = 'Updated' --Or whatever
END
ELSE
BEGIN
	Set @ReturnMessage = 'Already in use' --Or whatever
END

Answer 8

I ultimately used this with a catch on the db.cs and if "success" on the code behind. Here is the SQL:

USE [IPSDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [Examination].[IPS_STAFF_GET_PROFILE_BY_SSN] 
@SSN nvarchar(11),
@CJTC_ID nvarchar(8)

AS 
BEGIN
IF NOT EXISTS (SELECT * FROM IPS_APPLICANT_PROFILE WHERE SSN=@SSN)
BEGIN
UPDATE IPS_APPLICANT_PROFILE
SET 
SSN = @SSN
FROM IPS_APPLICANT_PROFILE
WHERE CJTC_ID = @CJTC_ID
END
ELSE
BEGIN
RAISERROR ('An Applicant already exists with SSN:', 16, 1 )
END
END

Comments/Concerns? Thanks again to everyone for your help :)

Answer 9

This code is OK although in a multi-user environment with many hits it may fail. To make it a bit more bullet-proof you can add the not exists condition into UPDATE statement as well and check if the UPDATE happened by the @@ROWCOUNT and notify the user if it didn't.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog