I had a request at work for a method of adding links at random. In other words, I have a group of people, say, 40 who want to add links to the group website without going through me, the web master.
Now, first thought is, BAD IDEA!!! All I need is a bunch of links on my group website that don't pertain to anything work related. I can just imagine links to the Seattle Times or to someone's online email account.
That being said, I did do the work on my personal website. The menu on the left of my website is dynamically generated from a SQL Server database so I don't need to update the links manually anymore, nor do I have to ftp my pages up to the server when I want to add a link.
I'm going to cover this in detail from beginning to end in multiple parts so bear with me here. In this part, I'm going to cover the database.
The database is more than just a table. I started with a table that has five columns. They are as follows:
- link_id int identity
- link_name varchar(36)
- link_description varchar(3000)
- link_url varchar(255)
- link_parent int default 0
Here is the code to create the table.
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'dbo.links')
BEGIN
DROP Table dbo.links
END
GO
CREATE TABLE dbo.links
(
link_id int primary key identity
, link_name varchar(36) not null
, link_description varchar(3000)
, link_url varchar(255)
, link_parent int default 0 not null
)
GO
As you can see, it is pretty simple.
The second part to the database is the queries and commands. I put everything into stored procedures for simplicity. Instead of weeding through code in multiple places, I just modify a stored procedure and everything gets the updates.
The first stored procedure was designed to return all the data from the table. It is written in such a way that we can pass the stored procedure values and it will filter based on the values passed to it.
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetLinks')
BEGIN
DROP Procedure dbo.GetLinks
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Ron Hagerman
-- Create date: 12/22/2007
-- Description: Returns a list of links.
-- =============================================
CREATE PROCEDURE dbo.GetLinks
@link_name varchar(36) =NULL,
@link_descr varchar(3000) =NULL,
@link_url varchar(255) =NULL,
@link_parent int =NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Select
link_name,
link_description,
link_url,
link_parent
from
links
where
(ISNULL(link_name,0) = ISNULL(@link_name,ISNULL(link_name,0)))
and
(ISNULL(link_description,0) = ISNULL(@link_descr,ISNULL(link_description,0)))
and
(ISNULL(link_url,0) = ISNULL(@link_url,ISNULL(link_url,0)))
and
(ISNULL(link_parent,0) = ISNULL(@link_parent,ISNULL(link_parent,0)))
END
GO
Now the first thing you may notice is the where clause. What it is doing is, if the field is null, set the value to 0. If the variable is null (no value was passed to the stored procedure) then set the value to the same value as the contents of the field. This equates to the value of the variable matching the value of the field if the variable is null so you always return true. (all records returned)
Let's go ahead and break down the where clause.
where
(ISNULL(link_name,0) = ISNULL(@link_name,ISNULL(link_name,0)))
ISNULL(link_name,0) just states that if the value returned from link_name is null, change it to 0.
ISNULL(@link_name,ISNULL(link_name,0))
This piece states that if @link_name is null, replace it with the value of link_name unless link_name is null, then you can replace it with 0.
This is just like stating, if @variable is null, the were clause needs to state where 1=1 which always returns true so it is just like ignoring the variable all together and returns all the rows without filtering.
The rest of the stored procedures will be similar in nature.