Setting up a store procedure...

I'm developing a library and want to display the alphabets across the
screen. When a user clicks on one of the alphabets I want all titles
beginning with that letter to appear on the screen.

How would I write this stored procedure?

My table is called Titles

Fields:
Title ID
Titles

Thanks!

Re: Setting up a store procedure...

Not sure how involved this requirement is. If you want to get the list of
alphabets from the existing titles, you can have simple query like:

SELECT DISTINCT LEFT( titles, 1 ) AS "alpha"
FROM tbl
ORDER BY "alpha" ;

In many cases, for fast searching and indexing, people implement other
alternatives. One variation is to have another table that is referenced back
to the titles table. Another one with smaller list could be a computed
column that is indexed with the expression: LEFT( titles, 1 ) etc.

If you want all the alphabets displayed regardless of existing titles, you
can easily generate them on the client side itself. Most web development
tools have build in functions that does this in a quick loop. If you want to
do this with t-SQL, you can use the CHAR() function on a simple query like:

SELECT CHAR( n )
FROM Nbrs
WHERE n BETWEEN 65 AND 90 ;

Regarding how the table Nbrs is created, see:
www.projectdmx.com/tsql/tblnumbers.aspx

--
Anith

Re: Setting up a store procedure...

You can simply pass the letter selected by the user as a parameter to the
stored procedure and then pull the list. The stored procedure can be
something like this:

CREATE PROCEDURE GetTitles(
@letter NVARCHAR(1))
AS
SELECT title_id, title
FROM Titles
WHERE title LIKE @letter + '%'
ORDER BY title

Anith has a good suggestion to create a computed column on LEFT(title, 1)
that is indexed. Then the WHERE filter will be computed_column = @letter.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Re: Setting up a store procedure...

On Dec 20, 2:51 pm, "Plamen Ratchev" wrote:
> You can simply pass the letter selected by the user as a parameter to the
> stored procedure and then pull the list. The stored procedure can be
> something like this:
>
> CREATE PROCEDURE GetTitles(
> @letter NVARCHAR(1))
> AS
> SELECT title_id, title
> FROM Titles
> WHERE title LIKE @letter + '%'
> ORDER BY title
>
> Anith has a good suggestion to create a computed column on LEFT(title, 1)
> that is indexed. Then the WHERE filter will be computed_column = @letter.
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com

Thanks Plamen this is easy enough and it works! Thanks Anith for your
suggestion too!