Skip to content
Advertisement

Get Current date, current month, current year depending on user selection – SQL stored procedure

I have a stored procedure that gives results depending on multiple values from the User(filtering), in my query I have 2 date columns date_start and date_end, I want to allow the user to return data depending on his selection from 2dropdownmenu one for start date and the other for end date as this: (this day – this month – this year – All Data).

I’m working Using C# winform, all the search part is done and it’s working, but I can’t figure out how to add the query to the filter depending on the value coming from the dropdownmenu.

current date : where date_start= cast(getdate() as Date)

current month: WHERE MONTH(date_start) = MONTH(CURRENT_DATE()) AND YEAR(date_start) = YEAR(CURRENT_DATE())

current year: where year(date_start) = year(getdate()) and datepart(dy, date_start) <= datepart(dy, getdate())

here is my stored producer:

ALTER procedure [dbo].[ContractsSearch]
(
@cust_id nvarchar (10) = NULL,
@name nvarchar (50) = NULL,
@shop_id nvarchar (10) = NULL,
@duration nvarchar (3) = NULL,
@usage nvarchar (20) = NULL,
@rent_type nvarchar (20) = NULL,
@date_start nvarchar (20)= NULL,
@date_end nvarchar (20)= NULL,
@archive bit=NULL
) AS
BEGIN
SET NOCOUNT ON;

SELECT dbo.contracts.ID, dbo.contracts.cust_id, dbo.customers.name, dbo.contracts.shop_id, dbo.contracts.duration, dbo.contracts.price, dbo.contracts.tax, dbo.contracts.usage, dbo.contracts.rent_type, dbo.contracts.price2, 
dbo.contracts.note2, dbo.contracts.date_start, dbo.contracts.date_end, dbo.contracts.note, dbo.contracts.app_user
FROM dbo.customers INNER JOIN
dbo.contracts ON dbo.customers.cust_id = dbo.contracts.cust_id
WHERE 
(contracts.cust_id LIKE N'%' + @cust_id + '%' OR @cust_id IS NULL)
AND
(customers.name LIKE N'%' + @name + '%' OR @name IS NULL)
AND
(contracts.shop_id = @shop_id OR @shop_id='' OR @shop_id IS NULL)
AND
(contracts.duration= @duration OR @duration='' OR @duration IS NULL)
AND
(contracts.usage = @usage OR @usage=N'إختر نوع الإستعمال' OR @usage IS NULL)
AND
(contracts.rent_type = @rent_type OR @rent_type=N'إختر نوع الايحار' OR @rent_type IS NULL)  
AND
(contracts.archive = @archive OR @archive IS NULL)
END

Advertisement

Answer

With the help of comments, and especially from @Charlieface I created the Procedure, Thank you all. Here is the final procedure, and I welcome any improvements suggestions:

ALTER procedure [dbo].[ContractsSearch]
(
@cust_id nvarchar (10) = NULL,
@name nvarchar (50) = NULL,
@shop_id nvarchar (10) = NULL,
@duration nvarchar (3) = NULL,
@usage nvarchar (20) = NULL,
@rent_type nvarchar (20) = NULL,
@date_start nvarchar (20) = NULL,
@date_end nvarchar (20) = NULL,
@archive bit=NULL
) AS
BEGIN
SET NOCOUNT ON;

DECLARE @StartDate1 as date = NULL
DECLARE @StartDate2 as date = NULL

if @date_start = N'اليوم الحالي'
BEGIN
set @StartDate1 = DATEADD(DAY, 0, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP))
set @StartDate2 = DATEADD(DAY, 1, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP))
END 
ELSE if @date_start = N'الشهر الحالي'
BEGIN
set @StartDate1 = dateadd(m, datediff(m, 0, GetDate()), 0)
set @StartDate2 = dateadd(m, datediff(m, -1, GetDate()), 0)
END 
ELSE if @date_start = N'السنة الحالية'
BEGIN
set @StartDate1 = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
set @StartDate2 = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1)
END 

DECLARE @EndDate1 as date = NULL
DECLARE @EndDate2 as date = NULL

if @date_end = N'اليوم الحالي'
BEGIN
set @EndDate1 = DATEADD(DAY, 0, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP))
set @EndDate2 = DATEADD(DAY, 1, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP))
END 
ELSE if @date_end = N'الشهر الحالي'
BEGIN
set @EndDate1 = dateadd(m, datediff(m, 0, GetDate()), 0)
set @EndDate2 = dateadd(m, datediff(m, -1, GetDate()), 0)
END 
ELSE if @date_end = N'السنة الحالية'
BEGIN
set @EndDate1 = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
set @EndDate2 = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1)
END 

DECLARE @sql nvarchar(max) = N'
SELECT dbo.contracts.ID, dbo.contracts.cust_id, dbo.customers.name, 
dbo.contracts.shop_id, dbo.contracts.duration, dbo.contracts.price, 
dbo.contracts.tax, dbo.contracts.usage, dbo.contracts.rent_type, 
dbo.contracts.price2, 
dbo.contracts.note2, dbo.contracts.date_start, dbo.contracts.date_end, 
dbo.contracts.note, dbo.contracts.app_user
FROM dbo.customers INNER JOIN
dbo.contracts ON dbo.customers.cust_id = dbo.contracts.cust_id WHERE 1 = 1'


+ CASE WHEN @cust_id !=NULL THEN
    N' AND contracts.cust_id LIKE char(37) + @cust_id + char(37)' ELSE N'' END
  + CASE WHEN @name !=NULL THEN 
    N' AND customers.name LIKE char(37) + @name + char(37) ' ELSE N'' END
  + CASE WHEN @shop_id !=NULL THEN
    N' AND contracts.shop_id = @shop_id' ELSE N'' END
  + CASE WHEN @duration !=NULL THEN
    N' AND contracts.duration= @duration' ELSE N'' END
  + CASE WHEN @usage !=NULL OR @usage != N'إختر نوع الإستعمال' THEN
    N' AND contracts.usage = @usage' ELSE N'' END
  + CASE WHEN @rent_type !=NULL OR @rent_type!= N'إختر نوع الايحار' THEN
    N' AND contracts.rent_type = @rent_type' ELSE N'' END
  + CASE WHEN @date_start !=NULL OR @date_start != N'تاريخ البدء'THEN
    N' AND contracts.date_start >= @StartDate1 and  contracts.date_start < @StartDate2' ELSE N'' END
  + CASE WHEN @date_end !=NULL OR @date_end !=N'تاريخ الإنتهاء' THEN
    N' AND contracts.date_end >= @EndDate1 and  contracts.date_end < @EndDate2' ELSE N'' END
  + CASE WHEN @archive !=NULL THEN
    N' AND contracts.archive = @archive' ELSE N'' END;

DECLARE @params nvarchar(max) = N'
@cust_id nvarchar (10),
@name nvarchar (50),
@shop_id nvarchar (10),
@duration nvarchar (3),
@usage nvarchar (20),
@rent_type nvarchar (20),
@StartDate1 date,
@StartDate2 date,
@EndDate1 date,
@EndDate2 date,
@archive bit';

--PRINT @sql;

EXEC sys.sp_executesql @sql, @params, 
@cust_id,
@name,
@shop_id,
@duration,
@usage,
@rent_type,
@StartDate1,
@StartDate2,
@EndDate1,
@EndDate2 ,
@archive;

END
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement