Writing stored procedure

Question

Hi,

I wanted to write stored procedure/SQL query with the following functionality:

• Select from two dates (basically range of dates) from Reports table
• Test type from Reports table
• Test project from TestProjects table

Can anybody please help me to write the query for the above functionality?
Kindly waiting for your response.

Thanks,
Santosh

Answer 1

Hi @Santosh Umarani ,

You could create a user-defined function as below:

CREATE FUNCTION [dbo].[SplitString]  
(  
    @List NVARCHAR(MAX),  
    @Delim VARCHAR(255)  
)  
RETURNS TABLE  
AS  
    RETURN ( SELECT [Value] FROM   
      (   
        SELECT   
          [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],  
          CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))  
        FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)  
          FROM sys.all_objects) AS x  
          WHERE Number <= LEN(@List)  
          AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim  
      ) AS y  
    );  

Then you could call this function as below:

 declare @startdate date  
 declare @enddate date  
 declare @projectname varchar(1000)  
      
 set @startdate='2021-01-01'  
 set @enddate='2021-03-01'  
 set @projectname='project1,project2,project3'  
  
 select a.TestType,b.TestProjectName  
 from [dbo].[Reports] a  
 left join [dbo].[TestProjects] b on a.TestProjectID=b.TestProjectID  
 where a.TestDate between @startdate and @enddate  
 and b.TestProjectName in (SELECT value FROM [dbo].[SplitString](@projectname, ','));   

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi ErlandSommarskog and Melissa,

Please find the create query for both the table.

CREATE TABLE [dbo].[Reports](
[TestReportID] [numeric](38, 0) IDENTITY(1,1) NOT NULL,
[TestCaseID] [int] NOT NULL,
[TestTypeID] [int] NOT NULL,
[ErrorTypeID] [int] NOT NULL,
[TestClassID] [int] NULL,
[TestProjectID] [int] NULL,
[TestDate] nvarchar NOT NULL,
[ErrorLogPath] nvarchar NULL,
[LatestReport] [bit] NULL,
[ExecutionTime] [int] NULL,
[BranchName] nvarchar NULL,
[LogData] [ntext] NULL,
[TestType] nvarchar NULL,
[DisplayError] nvarchar NULL,
[TestProject] nvarchar NULL,
[TestCaseUID] nvarchar NULL)

CREATE TABLE [dbo].[TestProjects](
[TestProjectID] [int] IDENTITY(1,1) NOT NULL,
[TestProjectName] nvarchar NOT NULL,
[TestClassID] [int] NOT NULL,
[FunctionalTopic] nvarchar NULL,
[TFSTeam] nvarchar NULL,
[CreatedOn] [datetime] NULL,
[Comment] nvarchar NULL)

To select two dates (basically range of dates) from Reports table, user will have option to select from and to date in UI.
Kindly waiting for your response.

Thanks,
Santosh

Answer 3

Hi @Santosh Umarani ,

Thanks for your update.

Could you please also provide some sample data for both tables and expected output?

Please also refer below example and check whether it is helpful to you.

declare @startdate date  
declare @enddate date  
  
set @startdate='2021-01-01'  
set @enddate='2021-03-01'  
  
select a.TestType,b.TestProjectName  
,*   
from [dbo].[Reports] a  
left join [dbo].[TestProjects] b on a.TestProjectID=b.TestProjectID  
where a.TestDate between @startdate and @enddate  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

@Santosh Umarani as per I get your requirement. Please find the below query:
Test type from Reports table

Test project from TestProjects table

Select R.[TestType], T.[TestProjectName]
FROM Reports AS R
INNER JOIN TestProjects AS T ON T.TestProjectID = R.TestProjectID
WHERE R.[TestDate] IN (' ' , ' ')

Please let me know if it is helpful

Thanks & Regards,
Shahzaib

Answer 5

Thanks Melissa. The query is working fine.

However, I wanted to pass list of project names from UI and for only those selected project names, I should execute the query.
Sorry I missed to inform in the beginning.
Kindly waiting for your response.