【SQL开源代码栏目提醒】:网学会员,鉴于大家对SQL开源代码十分关注,论文会员在此为大家搜集整理了“GolfArticles.sql”一文,供大家参考学习!
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spDeleteCustomer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spDeleteCustomer]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spSaveCustomer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spSaveCustomer]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spSelectArticle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spSelectArticle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spSelectCustomer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spSelectCustomer]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spSelectCustomerByUsername]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spSelectCustomerByUsername]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Article]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Article]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Customer]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Customer]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[States]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[States]
GO
CREATE TABLE [dbo].[Article] (
[ArticleID] [int] IDENTITY (1, 1) NOT NULL ,
[DateEntered] [datetime] NOT NULL ,
[ArticleDate] [datetime] NOT NULL ,
[Author] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ArticleText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Customer] (
[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
[CompanyName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Address] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactFirstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactLastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactEMail] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContractEnds] [datetime] NOT NULL ,
[ContractLevel] [int] NOT NULL ,
[UserName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateEntered] [datetime] NOT NULL ,
[DateModified] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[States] (
[StateID] [int] IDENTITY (1, 1) NOT NULL ,
[StateAbbreviation] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StateName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE spDeleteCustomer
@CustomerID int
AS
SET NOCOUNT ON
DELETE FROM Customer WHERE CustomerID=@CustomerID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE spSaveCustomer
@CustomerID int,
@CompanyName nvarchar(50),
@Address nvarchar(50),
@City nvarchar(50),
@State nvarchar(10),
@PostalCode nvarchar(20),
@ContractEnds datetime,
@ContactFirstName nvarchar(50),
@ContactLastName nvarchar(50),
@ContactEMail nvarchar(128),
@UserName nvarchar(50),
@Password nvarchar(50)
AS
SET NOCOUNT ON
DECLARE @Ret int
SELECT @Ret=CustomerID FROM Customer WHERE CustomerID=@CustomerID
IF IsNull(@Ret,0)=0
BEGIN
INSERT INTO Customer(
CompanyName ,
Address ,
City ,
State ,
PostalCode ,
ContractEnds ,
ContactFirstName ,
ContactLastName ,
ContactEMail ,
UserName ,
[Password] )
VALUES(
@CompanyName ,
@Address ,
@City ,
@State ,
@PostalCode ,
@ContractEnds ,
@ContactFirstName ,
@ContactLastName ,
@ContactEMail ,
@UserName ,
@Password )
-- Be careful about triggers and @@Identity
SET @Ret=@@Identity
END
ELSE
BEGIN
UPDATE Customer SET
CompanyName=@CompanyName ,
Address=@Address ,
City=@City ,
State=@State ,
PostalCode=@PostalCode ,
ContractEnds=@Contr