Post reply

retrieving all customers whose surname begins with a certain letter

  • February 15, 2009 at 3:22 pm

    #128744

    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

  • February 15, 2009 at 4:00 pm

    #944283

    [font="Verdana"]I suggest you look at LIKE. Look it up in SQL Server Books Online. It should do what you want.[/font]

  • February 15, 2009 at 4:04 pm

    #944284

    Thanks Bruce

    I applied the following and it worked

    WHEREexPlayerSurname LIKE COALESCE(@SearchText, '') + '%'

  • February 15, 2009 at 4:17 pm

    #944287

    [font="Verdana"]Perfect! :D[/font]

  • February 16, 2009 at 12:37 pm

    #944784

    Additionally, instead of using subqueries, try to rewrite the sentence using CTE.

  • February 16, 2009 at 9:48 pm

    #944934

    fergek (2/16/2009)

    Additionally, instead of using subqueries, try to rewrite the sentence using CTE.

    Why? Other than readability, it's not going to perform any better... it's just an inline view either way.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply