NULL is not stored as a value so operators =,> etc will not work with NULL under default conditions (ANSI NULL settings)so you should be using IS NULL,IS NOT NULL for comparison likeCREATE PROCEDURE [dbo].[usp_get_data] (@start_date datetime, @end_date datetime = null , @no_of_week int = null )ASBEGINIF @no_of_week is nullBEGINset @no_of_week = (select datepart(week, @end_date) - datepart(week, @start_date ) + 1 ) --- if user input is with end date and doesnot have @no_of_weekEND SET @week_value=(SELECT TOP 1 CASE WHEN @no_of_week=1 then [1_week]WHEN @no_of_week=2 then [2_weeks]WHEN @no_of_week =3 then [3_weeks]WHEN @no_of_week =4 then [4_weeks]WHEN @no_of_week between 5 and 24 then [5_24_weeks]WHEN @no_of_week between 25 and 52 then [25_52_weeks]endFROM pricing_matrix)SELECT * FROM fact_sales WHERE total_week = @week_valueEnd
------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs