【SQL开源代码栏目提醒】:文章导读:在新的一年中,各位网友都进入紧张的学习或是工作阶段。网学会员整理了SQL开源代码-club-add.sql的相关内容供大家参考,祝大家在新的一年里工作和学习顺利!
CREATE TABLE [Albums] (
[albumid] [int] IDENTITY (1, 1) NOT NULL ,
[title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[parentid] [int] NOT NULL ,
[private] [bit] NULL ,
[description] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ownerid] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [Announcements] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[itemdate] [datetime] NOT NULL ,
[title] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[description] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[staticURL] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[photo] [int] NOT NULL ,
[albumid] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [Events] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[starttime] [datetime] NOT NULL ,
[endtime] [datetime] NULL ,
[title] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[description] [varchar] (800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[staticURL] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[location] [int] NULL ,
[photo] [int] NOT NULL ,
[album] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [Locations] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[title] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[description] [varchar] (800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[linkURL] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[photo] [int] NOT NULL ,
[directions] [varchar] (800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[address] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [MemberInfo] (
[address] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[firstname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lastname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[avatar] [image] NULL ,
[memberid] [uniqueidentifier] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [images] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[largeimage] [image] NOT NULL ,
[thumbimage] [image] NULL ,
[origimage] [image] NULL ,
[album] [int] NOT NULL ,
[notes] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [Albums] ADD
CONSTRAINT [PK_Albums] PRIMARY KEY CLUSTERED
(
[albumid]
) ON [PRIMARY]
GO
ALTER TABLE [MemberInfo] ADD
CONSTRAINT [PK_MemberInfo] PRIMARY KEY CLUSTERED
(
[memberid]
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE AlbumList
(
@userid UNIQUEIDENTIFIER
)
AS
SELECT Albums.albumid, Albums.title, Albums.private, MIN(images.id) AS DisplayImage, COUNT(images.id) AS ImageCount, Albums.description
FROM Albums LEFT OUTER JOIN
images ON Albums.albumid = images.album
WHERE (Albums.private = 0) OR
(Albums.ownerid = @userid)
GROUP BY Albums.albumid, Albums.title, Albums.private, Albums.description
RETURN
GO
CREATE PROCEDURE MemberCountByLetter
AS
DECLARE @letter char(1), @index int, @max int
CREATE TABLE #results ( letter CHAR(1), num INT)
SET @index=ASCII('A')
SET @max=ASCII('Z')
WHILE @index <= @max
BEGIN
SET @letter = CHAR(@index)
INSERT INTO #results Select letter=@letter, num=count(*) from MemberInfo where SUBSTRING(lastname,1,1)=@letter
SET @index = @index + 1
END
Select * FROM #results
RETURN
GO
CREATE PROCEDURE NextAnnouncement
(
@id int
)
AS
/* SET NOCOUNT ON */
DECLARE @curr datetime
SELECT @curr = itemdate
FROM Announcements
WHERE (id = @id)
SELECT top 1 nextid= id
FROM Announcements
WHERE (itemdate > @curr OR
(itemdate = @curr) AND (id > @id)) order by itemdate asc, id asc
GO
CREATE PROCEDURE NextPrevAnnouncement
(
@id int,
@previd int output,
@nextid int output
)
AS
/* SET NOCOUNT ON */
DECLARE @curr datetime
SELECT @curr = itemdate
FROM Announcements
WHERE (id = @id)
SELECT @previd= id
FROM Announcements
WHERE (itemdate < @curr OR
(itemdate = @curr) AND (id < @id)) order by itemdate asc, id asc
SELECT @nextid= id
FROM Announcements
WHERE (itemdate > @curr OR
(itemdate = @curr) AND (id > @id)) order by itemdate desc, id desc
GO
CREATE PROCEDURE Ne