GetFractionInfo.sql

USE [Codigos]
GO

/****** Object:  StoredProcedure [dbo].[GetFractionInfo]    Script Date: 07/06/2010 08:58:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetFractionInfo]
	(
	@clientId int,
	@fraction nvarchar(8)
	)
AS
	SET NOCOUNT ON
	SELECT        f.Fraction, f.Sensible, COALESCE(cp.[Rule], '') AS [Rule], COALESCE(cp.ExpirationDate, GETDATE()) AS ExpirationDate, 
	COALESCE(cp.Charter, '') AS Charte, COALESCE(cp.Amount, 0) AS Amount
FROM            Fraction AS f LEFT OUTER JOIN
                         ClientProgram AS cp
              ON f.Fraction = cp.Fraction AND cp.ClientId = @clientId
WHERE        f.Fraction = @fraction
	RETURN

GO

InsInvoiceData.sql

USE [Codigos]
GO

/****** Object:  StoredProcedure [dbo].[InsInvoiceData]    Script Date: 07/06/2010 08:57:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InsInvoiceData]
	(
		@FileName nvarchar(50),
		@File image,
		@CustomDocumentDetailId int
	)
AS
	SET NOCOUNT ON
	
	DECLARE @FileSize int
    SET @FileSize = DATALENGTH(@File)

	INSERT INTO InvoiceData(FileName, FileSize, [File], CustomDocumentDetailId)
	 VALUES(@FileName, @FileSize, @File, @CustomDocumentDetailId)

	RETURN

GO

InvoiceData.sql

USE [Codigos]
GO

/****** Object:  Table [dbo].[InvoiceData]    Script Date: 07/06/2010 08:57:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[InvoiceData](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[CustomDocumentDetailId] [int] NOT NULL,
	[FileName] [nvarchar](50) NOT NULL,
	[FileSize] [int] NOT NULL,
	[File] [image] NOT NULL,
 CONSTRAINT [PK_InvoiceData] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[InvoiceData]  WITH CHECK ADD  CONSTRAINT [FK_InvoiceData_CustomDocumentDetail] FOREIGN KEY([CustomDocumentDetailId])
REFERENCES [dbo].[CustomDocumentDetail] ([Id])
GO

ALTER TABLE [dbo].[InvoiceData] CHECK CONSTRAINT [FK_InvoiceData_CustomDocumentDetail]
GO