Writing stored procedure

Santosh Umarani 81 Reputation points
2021-06-30T13:04:09.39+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,675 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-07-01T08:12:41.887+00:00

    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.

    0 comments No comments

7 additional answers

Sort by: Most helpful
  1. Santosh Umarani 81 Reputation points
    2021-07-01T05:07:51.077+00:00

    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

    0 comments No comments

  2. MelissaMa-MSFT 24,176 Reputation points
    2021-07-01T05:37:05.607+00:00

    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.

    0 comments No comments

  3. SHAH ZAIB 1 Reputation point
    2021-07-01T06:05:48.587+00:00

    @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

    0 comments No comments

  4. Santosh Umarani 81 Reputation points
    2021-07-01T07:08:52.6+00:00

    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.

    0 comments No comments