SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Stored procedure,
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Deezy1017
Starting Member

Jamaica
4 Posts

Posted - 11/20/2013 :  01:51:38  Show Profile  Reply with Quote
I'm trying to chech a see if a record(password) of individual have a number in it if not delete the record
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


S.Davis

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/20/2013 :  02:04:03  Show Profile  Reply with Quote
As per your explanation it should be this

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Deezy1017
Starting Member

Jamaica
4 Posts

Posted - 11/21/2013 :  02:12:39  Show Profile  Reply with Quote
ok

S.Davis
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 12/03/2013 :  11:34:55  Show Profile  Reply with Quote
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!!

Edited by - Kristen on 12/03/2013 11:37:34
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/03/2013 :  23:54:46  Show Profile  Reply with Quote
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!!


sorry I dont think thats what OP is loking 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

see this illustration


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



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 12/04/2013 :  04:05:46  Show Profile  Reply with Quote
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!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/04/2013 :  05:21:23  Show Profile  Reply with Quote
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!!


Yep...just like me
We can play guess games until OP provides us with clear explanation what the expected behavior is

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000