GO
/****** Object:  Table [dbo].[Category]    Script Date: 07/07/2009 20:51:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Category](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) COLLATE Modern_Spanish_CI_AS NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Customers]    Script Date: 07/07/2009 20:51:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customers](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) COLLATE Modern_Spanish_CI_AS NOT NULL,
	[CategoryId] [int] NOT NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Customers]  WITH CHECK ADD  CONSTRAINT [FK_Customers_Category] FOREIGN KEY([CategoryId])
REFERENCES [dbo].[Category] ([Id])

/****** Object:  StoredProcedure [dbo].[AddCustomers]    Script Date: 07/07/2009 20:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AddCustomers]
	(
	@Name varchar(50) ,
	@CategoryId int
	)
	
AS
    set @Name = @Name ;
    
	Insert into Customers 
	([Name], CategoryId) 
	values 
	(@Name, @CategoryId)
	
	SELECT SCOPE_IDENTITY() AS Id


	RETURN

GO
/****** Object:  StoredProcedure [dbo].[delCustomers]    Script Date: 07/07/2009 20:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[delCustomers]
	(
	@Id int ,
	@CategoryId int
	)
	
AS
	DELETE FROM Customers
	WHERE [Id] = @Id     
	RETURN

GO
/****** Object:  StoredProcedure [dbo].[getCustomersByName]    Script Date: 07/07/2009 20:52:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[getCustomersByName]
(
	@Name varchar(50)
)
AS
	SELECT * 
	FROM Customers
	WHERE [Name] like '%' +  @Name + '%'
	RETURN

GO
/****** Object:  StoredProcedure [dbo].[updCustomers]    Script Date: 07/07/2009 20:52:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[updCustomers]
	(
	@Id int ,
	@Name Varchar(50) ,
	@CategoryId int
	)
	
AS
	UPDATE Customers
	SET [Name] = @Name ,
	    [CategoryId] = @CategoryId
	WHERE [Id] = @Id   
	RETURN