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
 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
	 FROM Fraction WHERE Fraction = @fraction
END
	RETURN