search for numbered names - 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)

search for numbered names

Author

Topic

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-08-05 : 05:03:11

I have the following stored proc.
When the letter passed in is # or perhaps 0 I would like it to look for all numbered names, e.g. 16MITRE LTD
Any ideas how to do that?


--web_GetTopClientsInfo 'A'
ALTER PROCEDURE web_GetTopClientsInfo (
@letter varchar(1)
)

AS

SELECT 		Companies.[ID],
		CompanyName,
		AccountCode,
		ParentGroup,
		ClientSectors.Description,
		TAP,
		KAM 
FROM 		Companies 
INNER JOIN	ClientSectors ON Companies.ClientSector = ClientSectors.ID
WHERE 		CompanyName LIKE @letter+'%'
ORDER BY 	ParentGroup

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-05 : 05:33:01

Try

ALTER PROCEDURE web_GetTopClientsInfo (
@letter varchar(1)
)

AS

IF @letter in ('#','0')

SELECT 		Companies.[ID],
		CompanyName,
		AccountCode,
		ParentGroup,
		ClientSectors.Description,
		TAP,
		KAM 
FROM 		Companies 
INNER JOIN	ClientSectors ON Companies.ClientSector = ClientSectors.ID
WHERE 		CompanyName LIKE '[0-9][a-zA-Z]%'
ORDER BY 	ParentGroup

ELSE

SELECT 		Companies.[ID],
		CompanyName,
		AccountCode,
		ParentGroup,
		ClientSectors.Description,
		TAP,
		KAM 
FROM 		Companies 
INNER JOIN	ClientSectors ON Companies.ClientSector = ClientSectors.ID
WHERE 		CompanyName LIKE @letter+'%'
ORDER BY 	ParentGroup

Madhivanan

Failing to plan is Planning to fail

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-08-05 : 06:11:27

SQL doesn't seem to like the # character or maybe it's the website.
It's returning all results instead of filtered.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-05 : 06:39:25

How are you passing values to the procedure?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources