Null Value from View to Stored Procedure

[Lusitan]

Hello.

I've been hitting my head all day with this and maybe anyone can help me.

I have this piece of code on a view:

Select HA_Id, HA_Seg_Id, HA_Data
from dbo.Hist_Analysis
where (HA_Data BETWEEN CONVERT(DATETIME,'2001-01-01 00:00:00', 102) AND CONVERT(DATETIME,'2009-01-01 00:00:00', 102) AND (HA_Seg_Id IS NULL)

for giving me the results between these 2 dates and with no insurance (HA_Seg_Id). This works very well, giving backthe 3 expected records.

Now, If I pass this to a SP...

CREATE PROC A_01
(@Date_st smalldatetime, @Date_end smalldatetime, @Insurance bigint =null)
as
SELECT HA_Id as An, HA_Seg_Id as Insur, HA_Data as Date
from dbo.Hist_Cli
WHERE (HA_Seg_Id = @Insurance) AND (HA_Data>=@Date_st and HA_Data<=@Date_end)


... I have nothing. Any thoughts?

Thank you in advance.

 

[FoFa]

I think this is your problem: HA_Seg_Id = @Insurance
You have to use something like:
ISNULL(HA_Seg_Id,0) = ISNULL(@Insurance,0)

OR something likeL:

AND ((@insurance IS NULL AND HA_Seg_Id IS NULL) OR (@Insurance Is Not Null AND HA_Seg_Id Is Not NULl and HA_Seg_ID = @Insurance))

 

[Lusitan]

Yes, that was it. Thank you very much.