tricky stored procedure - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

tricky stored procedure

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-02-28 : 22:14:21

Well its tricky for me anyways. Basically this SP is pretty simple it checks to see if the nameOnline that is passed to it is taken or if it is available. The tricky thing is here, I just discovered a bug in my software.

The problem is this. For example if the username
BILLYBOB exists then --> if BILLYBOB1, BILLYBOB2, BILLYBOB3, BILLYBOB4, or BILLYBOB5 are passed to the SP then it must come back as found in the database as well. I'm thinking I somehow need to test if the last character is an int (1-5) and if it is then trim it then run the query. well at least in pseudocode, I am not sure how to do this in SQL.

Hope that makes sense :)

CREATE PROCEDURE select_nameOnline_taken
(
@nameOnline varchar(15)
)
AS
SET NOCOUNT ON
SELECT userID FROM tblUserDetails WHERE nameOnline = @nameOnline

GO

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-03-01 : 08:20:45

Add the following to the Stored Procedure and change the select statement:

DECLARE @name_len int
SET @name_len = LEN(@nameOnline)

SELECT userID FROM tblUserDetails WHERE Left(nameOnline,@name_len) = @nameOnline

Let me know if this does not work to your specifications.

Jeremy

** Not sure why the information in the Left function appears as a link.

Edited by - joldham on 03/01/2002 08:22:15

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-03-01 : 15:06:58

quote:
** Not sure why the information in the Left function appears as a link.

The forum software sees that you have something then the @ and something else, without spaces, so it tries to be smart and says, that must be an email address. I'll make it a mailto: link!

------------------------
GENERAL-ly speaking...

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-03-01 : 15:08:10

Mike, I haven't used it much, but you might want to look into the soundex feature in BOL and see if this gives you a close enough match without being too close to others.

------------------------
GENERAL-ly speaking...

Nazim
A custom title

1408 Posts

Posted - 2002-03-02 : 08:04:12

Mike, Try this

CREATE PROCEDURE select_nameOnline_taken
(
@nameOnline varchar(15)
)
AS
SET NOCOUNT ON
if isnumeric(right(@nameonline,1)) =1
SELECT userID FROM tblUserDetails WHERE nameOnline = substring(@nameOnline,1,
len(@nameOnline)-1)
else
SELECT userID FROM tblUserDetails WHERE nameOnline = @nameOnline

GO

HTH

--------------------------------------------------------------

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-03-06 : 01:29:20

great nazim I was able to implement yours and it worked (again!) :D

from what I can see this will test for each username with a (1-9) after it ?

Just clearing things up.

Is there a simple way to only check (1-5), if not this solution still works great

Thanks again

Nazim
A custom title

1408 Posts

Posted - 2002-03-06 : 01:51:35

It checks from 0-9 and some special characters too. you can modify it to check only 1-5 by using >= and <= or between.

Here it is

CREATE PROCEDURE select_nameOnline_taken
(
@nameOnline varchar(15)
)
AS
SET NOCOUNT ON
if right(@nameonline,1) between 1 and 5
SELECT userID FROM tblUserDetails WHERE nameOnline = substring(@nameOnline,1,
len(@nameOnline)-1)
else
SELECT userID FROM tblUserDetails WHERE nameOnline = @nameOnline

GO

you can play with between for the range of no.'s you want.

HTH

--------------------------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources