CREATE PROCEDURE CHECK_INDIVIDUAL
(
@username VARCHAR(50)
)

AS
BEGIN
DECLARE @PWD VARCHAR(50) 
SET @PWD=(SELECT Password_1 FROM Individual WHERE Password_1 =@username)

IF(@PWD NOT LIKE '%[^0-9]%')
BEGIN
SELECT'Passowrd Valid'
END

ELSE
BEGIN
SELECT'Password must Contain a number!'
//ERROR LINE DELETE Password_1 FROM Individual WHERE Username=@username
SELECT'Please re-enter your passwrod' 
END

END

quote:

---

Originally posted by visakh16

IF(@PWD NOT LIKE '%[^0-9]%')

---

IF(@PWD NOT LIKE '%[0-9]%')
	... invalid ...
ELSE
	... valid ...

SET @PWD=(SELECT Password_1 FROM Individual WHERE Password_1 =@username)

SET @PWD=(SELECT Password_1 FROM Individual WHERE Username = @username)

IF EXISTS(
	SELECT	*
	FROM	Individual
	WHERE	    Username = @username
		AND Password_1 NOT LIKE '%[0-9]%'
)
BEGIN
	SELECT 'Password Valid'
END
ELSE
BEGIN
	SELECT 'Password must Contain a number!'
//ERROR LINE DELETE Password_1 FROM Individual WHERE Username=@username
	SELECT 'Please re-enter your password' 
END

UPDATE	U
SET	Password_1 = NULL
FROM	Individual AS U
WHERE	    Username = @username
	AND Password_1 NOT LIKE '%[0-9]%'
IF @@ROWCOUNT = 0
BEGIN
	SELECT 'Password Valid'
END
ELSE
BEGIN
	SELECT 'Password must Contain a number!'
	SELECT 'Please re-enter your password' 
END

DELETE	U
FROM	Individual AS U
WHERE	    Username = @username
	AND Password_1 NOT LIKE '%[0-9]%'
IF @@ROWCOUNT = 0
...

quote:

---

Originally posted by Kristen

quote:

---

Originally posted by visakh16

IF(@PWD NOT LIKE '%[^0-9]%')

---

Should be:

IF(@PWD NOT LIKE '%[0-9]%')
	... invalid ...
ELSE
	... valid ...

??

SET @PWD=(SELECT Password_1 FROM Individual WHERE Password_1 =@username)

should probably be:

SET @PWD=(SELECT Password_1 FROM Individual WHERE Username = @username)

personally I would code it as follows:

IF EXISTS(
	SELECT	*
	FROM	Individual
	WHERE	    Username = @username
		AND Password_1 NOT LIKE '%[0-9]%'
)
BEGIN
	SELECT 'Password Valid'
END
ELSE
BEGIN
	SELECT 'Password must Contain a number!'
//ERROR LINE DELETE Password_1 FROM Individual WHERE Username=@username
	SELECT 'Please re-enter your password' 
END

not sure your DELETE code is what you want. Do you want to delete the ROW from INDIVIDUAL table? or just set their password to NULL?

Taking that into account I would actually code it as:

UPDATE	U
SET	Password_1 = NULL
FROM	Individual AS U
WHERE	    Username = @username
	AND Password_1 NOT LIKE '%[0-9]%'
IF @@ROWCOUNT = 0
BEGIN
	SELECT 'Password Valid'
END
ELSE
BEGIN
	SELECT 'Password must Contain a number!'
	SELECT 'Please re-enter your password' 
END

If you actually want to delete the row from Individual table then replace the UPDATE with a DELETE

DELETE	U
FROM	Individual AS U
WHERE	    Username = @username
	AND Password_1 NOT LIKE '%[0-9]%'
IF @@ROWCOUNT = 0
...

this code also takes care of the case that @username has more than one record, where one, or more, of the records does not contain a digit. Even if Username column is unique I prefer this style of programming as it is "defensive" - e.g. if the UNIQUE constraint ever got dropped by accident, or ... whatever!!

---

declare @t table
(
val varchar(100)
)
insert @t
values ('1243214'),
('76tr0980'),
('76576wrr'),
('786876@hhhj'),
('768757'),
('432432'),
('9879ghg')

SELECT *,
CASE WHEN val NOT LIKE '%[0-9]%' THEN 'Invalid' ELSE 'Valid' END AS KristenLogic,
CASE WHEN val NOT LIKE '%[^0-9]%' THEN 'valid' ELSE 'Invalid' END AS VisakhLogic
FROM @t
 

val	        KristenLogic	VisakhLogic
1243214	        Valid	        valid
76tr0980	Valid	        Invalid
76576wrr	Valid	        Invalid
786876@hhhj	Valid	        Invalid
768757	        Valid	        valid
432432	        Valid	        valid
9879ghg	        Valid	        Invalid

quote:

---

Originally posted by visakh16


sorry I dont think thats what OP is looking at as per this

I'm trying to chech a see if a record(password) of individual have a number in it if not delete the record

---

DELETE Password_1 FROM Individual WHERE Username=@username

quote:

---

see this illustration

---

quote:

---

Originally posted by Kristen

quote:

---

Originally posted by visakh16


sorry I dont think thats what OP is looking at as per this

I'm trying to chech a see if a record(password) of individual have a number in it if not delete the record

---

I agree, that is how I read that too. However, this code

DELETE Password_1 FROM Individual WHERE Username=@username

looks more like the O/P is wanting to UPDATE the table to remove the password, rather than delete the row. Plus, I don't know what is in the [Individual] table, but from the name it sounds like more attributes than just password, and deleting your personal record 'coz you entered a duff password sounds a bit severe! But I'm guessing what the O/P's requirements actually are.

quote:

---

see this illustration

---

Dunno what I was thinking of, but I probably misread the requirement. I was worrying that your code would not KEEP an all-alpha password, now re-reading the question its the other way round - only keep if it contains a number. But my guess is that the O/P's requirement is Some Numbers and Some Letters ... but there I go guessing again!!

---