Sql - Stored Procedure with multiple if condition

This is the table I have Created with the records

CREATE TABLE [dbo].[Graph](
[RequestID] [int] NOT NULL,[ModuleName] [nvarchar](50) NULL,[Info] [float] NULL,[HR] [float] NULL,[SR] [float] NULL, [ISIM] [float] NULL,[RequestCreatedDate] [datetime2](7) NULL,[RequestModifiedDate] [datetime2](7) NULL,

I have tried to create a Stored procedure with multiple 'if' conditions like

1.ModuleName = null, it has to calculate the average of columns 'Info',' HR',' SR', and 'ISIM' from start to end.

2.ModuleName = CSE, it has to calculate the average of columns 'Info',' HR',' SR', and 'ISIM' of module name with CSE only.

3.if start_date = null, end_date = null means it has to show all the requests from the start of the requestcreateddate to the till date.

4.if start_date = some date(eg02-02-2023), end_date = null means it has to show the requests from that particular start_date to the till date.

5.if start_date = some date (eg 02-02-2023), end_date = some date (eg 30-04-2023) means it has to show only the request created in between that particular start_date and end_date.

6.if start_date = null and end_date = some date (30-04-2023) means it has to show the request created from the start to the end_date.

I just tried writing this stored procedure, but I exactly don't know how to write a query for these conditions, can someone help me in writing a stored procedure query including these conditions? I'm a beginner in this. Thanks.

Alter proc avg_calc (
@module_name  nvarchar(max) = Null,@start_date datetime2 = null,
@end_date datetime2 = null)
As
Begin
Declare @sql nvarchar(max), @where nvarchar(max) = '' , @where1 datetime2 = ''
    set @sql = 'Select AVG(Info) as Info,
	AVG([HR]) as [HR],
	AVG([SR]) as [SR],
	AVG([ISIM]) as [ISIM]
FROM Graph'
If @module_name is not null
set @where = @where + 'AND ModuleName = @_module_name'
If @start_date is not null 
set @where1 = @where1 + 'AND RequestCreatedDate = @_start_date '
If @end_date is not null 
set @where1 = @where1 + 'AND RequestModifiedDate = @_end_date '

If LEN(@where) > 0
Set @sql = @sql + 'WHERE' + RIGHT(@where, LEN(@where)-5)
If LEN(@where1) > 0
Set @sql = @sql + 'WHERE' +RIGHT(@where1, LEN(@where1)-10)

Exec sp_executesql @sql, N'@_module_name nvarchar(100)', 
@_module_name = @module_name

Exec sp_executesql @sql, N'@_start_date datetime2, @_end_date datetime2', 
@_start_date = @start_date, @_end_date = @end_date

End 


EXEC avg_calc @module_name='CSE'

EXEC avg_calc @start_date = 02-02-2023, @end_date = 30-02-2023

I don't think you need to use dynamic SQL for this task. You could simply use something like this:

CREATE PROCEDURE avg_calc (
@module_name  nvarchar(max) = Null,
@start_date datetime2 = null,
@end_date datetime2 = null)
AS
SELECT AVG(Info) as Info,
	AVG([HR]) as [HR],
	AVG([SR]) as [SR],
	AVG([ISIM]) as [ISIM]
FROM Graph
WHERE (ModuleName = @module_name OR @module_name IS NULL)
AND (RequestCreatedDate >= @start_date OR @start_date IS NULL)
AND (RequestCreatedDate <= @end_date OR @end_date IS NULL)
OPTION (RECOMPILE)

I have used OPTION (RECOMPILE) to try to use an optimal execution plan for each execution, assuming that this procedure is not executed very often, but the table size is quite big (so the compilation time would be negligible compared to the execution time) and there are some indexes on ModuleName and RequestCreatedDate. If this assumption is incorrect, you should remove OPTION (RECOMPILE).

For more information, please read https://www.sommarskog.se/dyn-search.html.

You have sp_executesql twice, plus @where1 is a datetime2 which makes no sense.

Furthermore, it's unclear which date field you want to filter. You have two and are filtering against one for the start and the other for the end.

Pay attention to data types: is @module_name supposed to be nvarchar(max) or nvarchar(100)?

CREATE OR ALTER PROC avg_calc
  @module_name nvarchar(100) = NULL,
  @start_date datetime2 = NULL,
  @end_date datetime2 = NULL
AS

DECLARE @sql nvarchar(max);
SET @sql = '
SELECT
    AVG(Info) AS Info,
    AVG(HR) AS HR,
    AVG(SR) AS SR,
    AVG(ISIM) AS ISIM
FROM Graph
WHERE 1=1';

IF @module_name IS NOT NULL
    SET @sql += '
  AND ModuleName = @module_name';

IF @start_date IS NOT NULL
    SET @sql += '
  AND RequestCreatedDate >= @start_date';

IF @end_date IS NOT NULL
    SET @sql += '
  AND RequestCreatedDate < @end_date';

PRINT @sql;  -- your friend

EXEC sp_executesql @sql,
  N'@module_name nvarchar(100),
    @start_date datetime2,
    @end_date datetime2', 
  @module_name = @module_name
  @start_date = @start_date,
  @end_date = @end_date;

Also note the use of >= and < for the dates. For example, if you want results for the whole of 2023-05-10 you should pass in 2023-05-10 00:00:00 and 2024-05-11 00:00:00.