convert the below stored procedure into query

This is a procedure in my application.

The task of this procedure is when user gives parameter values from front end. It needs to display result accordingly.

Parameters:

For parameters @Site

From front end window he can give only one parameter value from multiple parameter values in drop down list. site column is there in existing table which is filtered by this parameter in query

For parameters @comm_group1, @comm_group2, @productcode, @productfamily

 

He can select a single parameter value or multiple parameter values or if he selects %

In front end window for these parameters, all the values of particular column should be select.

 Like

For @comm_group1, if he selects s05 as parameter value need to display result for this column value only or if he selects s05,s06  need to display result for these values of comm_group1 column or if he selects %, need to display result for all values of comm_group1. Comm_group1 column is there in table. This column is filtered by @comm_group1 parameter value in procedure.

I used comma separated values into rows function

select* from [dbo].[CSVToList]('so5,so6,s07')

in front end, he gives s05,s06,s07 for this parameter

which gives separate row values(s05 one row,s06 another row, s07 another row) like 3 rows here comes

Now I need to convert this stored procedure into query itself for my new application modified version because it won’t support stored procedures or functions.

Give me any suggestions.

USE[CMMS_ENB_REPORTS]

GO

/****** Object:  StoredProcedure [dbo].[USP_ENV_StockChanges]    Script Date: 5/6/2015 12:45:54 PM ******/

SETANSI_NULLS ON

GO

SETQUOTED_IDENTIFIER ON

GO

    /*

--Report Name     : Stock Changes(01.04)

--Created Date  : 2013-11-11 12:00:27.410

--Purpose             : --

--Modified Date :2014-05-14

--Purpose        : Added previous values

 

*/

-- [USP_ENV_StockChanges] 'SLV' ,'%','%' ,'%','%'

-- [USP_ENV_StockChanges] '{Site}' , '{COMM_GROUP_1}','{COMM_GROUP_2}','{Product_code}','{Product_family}'

ALTERproc [dbo].[USP_ENV_StockChanges]

  @site nvarchar(5),

  @comm_group1 nvarchar(10)=null ,

  @comm_group2 nvarchar(10)=null,

  @productcode nvarchar(10)=null,

  @productfamily nvarchar(10)=null

as

 

 if @comm_group1='%'

   set @comm_group1=null

 if @comm_group2='%'

   set @comm_group2=null

 if @productcode='%'

   set @productcode=null

 if @productfamily='%'

   set @productfamily=null

SELECTdistinct [SITE]

      ,[YEAR]

      ,[MONTH]

      ,[PERIOD]

      ,[INVENTORY_UOM]

      ,[PART_NO]

      ,[ACCOUNTING_GROUP]

      ,[ACCOUNTING_GROUP_DESC]

      ,[ASSET_CLASS]

      ,[PRODUCT_CODE]

      ,[PRODUCT_CODE_DESC]

      ,[PRODUCT_FAMILY]

      ,[PRODUCT_FAMILY_DESC]

      ,[COMM_GROUP_1]

      ,[COMM_GROUP_1_DESC]

      ,[COMM_GROUP_2]

      ,[COMM_GROUP_2_DESC]

      ,[QUANTITY]

      ,[TOTAL_INVENTORY_VALUE] [CURRENT_TOTAL_INVENTORY_VALUE]

      ,[TOTAL_INVENTORY_VALUE_$] [CURRENT_TOTAL_INVENTORY_VALUE_$]

             ,[PRE_TOTAL_INVENTORY_VALUE] [PREVIOUS_TOTAL_INVENTORY_VALUE]

      ,[PRE_TOTAL_INVENTORY_VALUE_$] [PREVIOUS_TOTAL_INVENTORY_VALUE_$]

      ,[PART_DESCRIPTION]

   FROM  [dbo].[ENV_StockChanges_V2]

  Where [site] = @site

  and ( COMM_GROUP_1 in (SELECT * FROM dbo.CSVToLIst(@comm_group1))  or @comm_group1 is null )

  and ( COMM_GROUP_2 in (SELECT * FROM dbo.CSVToLIst(@comm_group2))  or @comm_group2 is null)

  and ( PRODUCT_CODE in (SELECT * FROM dbo.CSVToLIst(@productcode))  or @productcode is null)

  and ( PRODUCT_FAMILY in(SELECT * FROM dbo.CSVToLIst(@productfamily))  or @productfamily is null)

  

  order by PERIOD