convert the below stored procedure into query

try this.... you can still use most of the query but instead of parameters, you would use variables....

declare @site nvarchar(5),
declare @comm_group1 nvarchar(10)=null ,
declare @comm_group2 nvarchar(10)=null,
declare @productcode nvarchar(10)=null,
declare @productfamily nvarchar(10)=null

set @site='somevalue'
set @comm_group1='somevalue1,somevalue2'
set @comm_group2='somevalue1,somevalue2'
set @productcode='somevalue'
set @productfamily='somevalue'

 begin
 if @comm_group1='%'
   set @comm_group1=null
 if @comm_group2='%'
   set @comm_group2=null
 if @productcode='%'
   set @productcode=null
 if @productfamily='%'
   set @productfamily=null
SELECT distinct [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)

End

---

Hope it Helps!!