retrieving all customers whose surname begins with

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 2005 Forums

Transact-SQL (2005)

retrieving all customers whose surname begins with

Author

Topic

monfu
Yak Posting Veteran

81 Posts

Posted - 2009-02-15 : 17:22:58

Dear All,

I wish to create a stored procedure whereby I will retrieve either all the lastNames if the parameter passed to the stored proc is blank, or else display the lastNames that start with the letter I pass to the stored procedure.

I created something like this at the moment:-

ALTER PROCEDURE [dbo].[HS_ExPlayers_GetExPlayers]
(
@PageIndex int,
@PageSize int,
@SearchText nvarchar(1)
)
AS
SET NOCOUNT ON

SELECT * FROM
(
SELECT hep.exPlayerID, hep.exPlayerName, hep.exPlayerSurname, hep.exPlayerSurname + ' ' + hep.exPlayerName AS exPlayerFullName, hep.exPlayerSeasons, hep.exPlayerProfile,
ROW_NUMBER() OVER (ORDER BY hep.exPlayerSurname ASC) AS RowNum
FROM dbo.HS_ExPlayers hep
) ExPlayers
WHERE ExPlayers.RowNum BETWEEN (@PageIndex*@PageSize+1) AND ((@PageIndex+1)*@PageSize)
AND (@SearchText <> '' AND ExPlayers.exPlayerSurname >= ('C') AND ExPlayers.exPlayerSurname <= ('E'))
ORDER BY exPlayerSurname ASC

This will retrieve the LastName 'D'

However I wish to pass just one letter (D), instead of 'C' and 'E', to retreive the players with surname D

Is there anyway I can achieve that?

Thanks for your help

Johann

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-15 : 17:42:30

[code]ALTER PROCEDURE dbo.HS_ExPlayers_GetExPlayers
(
@PageIndex INT,
@PageSize INT,
@SearchText NVARCHAR(1)
)
AS

SET NOCOUNT ON

SELECT exPlayerID,
exPlayerName,
exPlayerSurname,
exPlayerFullName,
exPlayerSeasons,
exPlayerProfile
FROM (
SELECT exPlayerID,
exPlayerName,
exPlayerSurname,
exPlayerSurname + ' ' + hep.exPlayerName AS exPlayerFullName,
exPlayerSeasons,
exPlayerProfile,
ROW_NUMBER() OVER (ORDER BY exPlayerSurname ASC) AS RowNum
FROM dbo.HS_ExPlayers
WHERE exPlayerSurname LIKE COALESCE(@SearchText, '') + '%'
) AS ExPlayers
WHERE RowNum BETWEEN @PageIndex * @PageSize + 1 AND (@PageIndex + 1) * @PageSize
ORDER BY exPlayerSurname[/code]

E 12°55'05.63"
N 56°04'39.26"

monfu
Yak Posting Veteran

81 Posts

Posted - 2009-02-15 : 17:47:40

Excellent thanks!

That worked brilliantly

Thanks for your help Peso

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-16 : 08:10:15

or

WHERE @SearchText IS NULL OR exPlayerSurname LIKE @SearchText+'%'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources