SQL storedprocedure to search a string in a column

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

SQL storedprocedure to search a string in a column

Author

Topic

lann
Starting Member

2 Posts

Posted - 2009-03-13 : 05:43:17

I searched the forum but couldn't find an exact match of my requirements.
Apologies if I had created a new thread for an existing discussion

I have a table credentials.In credentials table I have 2 columns LoginID nvarchar(225)and password nvarchar (15).
I would like to have a SQL stored procedure that takes login ID and password as parameters and check whether the entered LoginID is existing in the loginID column of the credentials table. If the login ID is not existing i like to give the user some message directing to register.if it is there and the entered password do not match i need to give a message "password invalid".If both are fine i need to give success message.
Please help.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-13 : 05:55:01

create proc usp_sample
( @loginid nvarchar(225),password nvarchar (15)
)
as
SET NOCOUNT ON
BEGIN

select case when loginid = @loginid and password = @password then 'success'
when loginid = @loginid then 'invalid login'
when password = @password then 'invalid password'
end as message from credentials

END
SET NOCOUNT OFF

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-03-13 : 05:59:41

Use charindex() function

Select Charindex(<search string>,<columnname>,starting_position)

If it returns >0 then the string is match!..

Regards

Senthil.C
Willing to update...

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-13 : 06:05:50

One normally uses raiseerror for this.

CREATE procedure LoginCheck
@username varchar(500), @passwd varchar (500)
as 
BEGIN

	DECLARE @DBID INT
	SET @DBID = DB_ID()
	
	DECLARE @DBNAME NVARCHAR(128)
	SET @DBNAME = DB_NAME()
	
	IF NOT exists (select 1 from credentials where LoginID =@username)
		BEGIN
		RAISERROR ('Please Register',16, 1, @DBID, @DBNAME)
		GOTO e
	END
	
	IF NOT exists (select 1 from credentials where LoginID =@username and [password]=@passwd)
		BEGIN
		RAISERROR ('Invalid Password',16, 1, @DBID, @DBNAME)
		GOTO e
		END
	
	IF  exists (select 1 from credentials where LoginID =@username and [password]=@passwd)
		BEGIN
		RAISERROR('Success',16, 1, @DBID, @DBNAME)
		GOTO e
		END
	
	E:
END

lann
Starting Member

2 Posts

Posted - 2009-03-13 : 08:06:12

Hi sakets,
Thanks for the response!!
Can I have a few more details -
Iam not familiar with the stored procedures.
It would be of great help to me if you could explain me significance of the following in the code
DECLARE @DBID INT
SET @DBID = DB_ID()

DECLARE @DBNAME NVARCHAR(128)
SET @DBNAME = DB_NAME()

Correct me if i am wrong-I guess these 2 variables should be the ones to hold the messages"success" or whatever.
RAISERROR ('Invalid Password',16, 1, @DBID, @DBNAME).Please shed some light on the parameters inside RAISEERROR.
Thanks.
Lann

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-13 : 08:24:31

DB_ID() and DB_NAME() are just functions giving info on database. You needn't have them if you want.

Go thru this to learn about raiserror and how to use it,

http://msdn.microsoft.com/en-us/library/ms178592.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources