GetFractionInfo.sql

ALTER PROCEDURE [dbo].[GetFractionInfo]
	(
	@clientId int,
	@fraction nvarchar(8)
	)
AS

SET NOCOUNT ON
DECLARE @NOTFOUND int

SELECT @NOTFOUND = COUNT(1) FROM ClientProgram cp WHERE cp.Fraction = @fraction AND cp.ClientId = @clientId

IF @NOTFOUND > 0
BEGIN
SELECT COALESCE(cp.Fraction, (SELECT TOP 1 Fraction FROM Fraction WHERE Fraction = @fraction)) AS Fraction, 
	CAST(COALESCE((SELECT TOP 1 Sensible FROM Fraction WHERE Fraction = @fraction), 0) AS bit) AS Sensible, 
	COALESCE(cp.[Rule], '') AS [Rule], COALESCE(cp.ExpirationDate, GETDATE()) AS ExpirationDate, 
	CAST(COALESCE(cp.Charter, '') AS bit) AS Charte, COALESCE(cp.Amount, 0.0) AS Amount, COALESCE(cp.Prosec, '') AS Prosec, COALESCE(cp.Immex, '') AS Immex
 FROM ClientProgram cp WHERE cp.Fraction = @fraction AND cp.ClientId = @clientId
END
ELSE
BEGIN
	SELECT Fraction, Sensible, '' AS [Rule], GETDATE() AS ExpirationDate, CAST(0 AS bit) AS Charter, 0.0 AS Amount, '' AS Prosec, CAST(0 AS bit) AS Immex
	 FROM Fraction WHERE Fraction = @fraction
END
	RETURN

GO

InsClientProgram.sql

ALTER PROCEDURE [dbo].[InsClientProgram]
	(
		@ClientId int,
		@Fraction varchar(8),
		@Rule varchar(20),
		@Charter bit,
		@Prosec nvarchar(50),
		@Immex bit,
		@Amount money,
		@ExpirationDate datetime,
		@Enable bit,
		@CreatedBy int,
		@CreatedOn datetime
	)
AS

	INSERT INTO ClientProgram(ClientId, Fraction, [Rule], Charter, Prosec, Immex, Amount, ExpirationDate, Enable, CreatedBy, CreatedOn)
	 VALUES(@ClientId, @Fraction, @Rule, @Charter, @Immex, @Prosec, @Amount, @ExpirationDate, @Enable, @CreatedBy, @CreatedOn)
	 
	SELECT * FROM ClientProgram WHERE Id = @@Identity

	RETURN


GO

UpdClientProgram.sql

ALTER PROCEDURE [dbo].[UpdClientProgram]
	(
		@Id int,
		@Fraction varchar(8),
		@Rule varchar(20),
		@Charter bit,
		@Prosec nvarchar(50),
		@Immex bit,
		@Amount money,
		@ExpirationDate datetime,
		@Enable bit,
		@UpdatedBy int,
		@UpdatedOn datetime
	)
AS

	UPDATE ClientProgram SET Fraction = @Fraction, [Rule] = @Rule, Charter = @Charter, Prosec = @Prosec,
		Immex = @Immex, Amount = @Amount, ExpirationDate = @ExpirationDate, Enable = @Enable, 
		UpdatedBy = @UpdatedBy, UpdatedOn = @UpdatedOn
	 WHERE Id = @Id

	RETURN


GO