A-Z Stored Procedure

[Mayoor]

Hi does anyone know what the best way is to have a list of links a-z which then in turn list the values of a column in a DB.

For instance say my column has 5 values (abba,bacon,croutons,deer,egg)

I want some links like this. So if I was to click 'a' the web page would bring up "abba"

a-b-c-d-e-f

Not sure if this is the correct forum, but im guessing a stored proc is required.

Any help would be greatfully appreciated!

 

[SQLDenis]

Your design is very bad for something like this
it would be better to normalize this data and then you can do select * from table where field like 'a%'

if you don't want to change your design then do a search for a split function on this forum and that function will return a table and you can then select from that table

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Mayoor]

Probably best if he passes the letter in as a parameter, any ideas how this is done?

 

[vongrunt]

if you don't want to change your design then do a search for a split function on this forum and that function will return a table and you can then select from that table

set crystal_ball on
go

And then requirement would soon extend over many rows at once, and then split() will not be enough - unless we are talking SQL2005, right?

set crystal_ball off
go

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[adamroof]

heres one of my SPs..the @URL and @group are parameters that are passed in (between create proc and AS, thats where you put them)

To run from Query Analyzer.

exec sp_Links '

http://mycoolsite/index.aspx',

'2'

To run from coding...
Depends on your language you are writing in...lemme know if its asp.net vb, c# and ill help ya, maybe some others too.

CREATE PROCEDURE dbo.sp_Links
	@URL varchar(100),@group int
AS

SET NOCOUNT ON

SELECT * 
FROM tblPageLinks 
WHERE linkPage =@URL
AND linkAuth <= @group
ORDER BY linkCategory,linkOrder

SET NOCOUNT OFF
GO

 

[Mayoor]

Adam the language is Classic ASP. I think each letter link will be appended with the querystring something like this

http://www.mysite.com/processingpage.asp?letter=a

http://www.mysite.com/processingpage.asp?letter=b

http://www.mysite.com/processingpage.asp?letter=c

http://www.mysite.com/processingpage.asp?letter=d

and so on.....

 

[adamroof]

been a while for classic asp, and dont have any pages running to test, however the SQL is tested as working.
Assuming your connections are similar. Let me know if this helps or hurts!

In SQL...

CREATE PROCEDURE sp_NameYourSPHere
    @atoz varchar(2)
AS

SET NOCOUNT ON

SET @atoz = @atoz + '%'
SELECT * 
FROM tblLinks 
WHERE linkName LIKE @atoz
ORDER BY linkName 

SET NOCOUNT OFF
GO


In ASP...

If Request.QueryString("letter") <> "" Then
 Set oCommand = Server.Createobject("Adodb.Command")
   oCommand.ActiveConnection=oConn
   oCommand.CommandType=adCmdStoredProc
   oCommand.CommandText = "sp_NameYourSPHere"
 Set param = oCommand.CreateParameter("@atoz", AdVarchar, adParamInput, 2,Request.QueryString("letter"))
   oCommand.Parameters.Append(param)
 set rsYourRecordset = oCommand.Execute()
Else
 'handle errors here
End If

 

[Mayoor]

Thats great thanks!