I am having a bit of trouble with MS SQL and PHP. Would you guide me with this?
Here is my story:
I have a delivery report. The report have filters. Take a look at my form:
Now the filters are working fine. I can retrieve data. But my boss wants my filter to be multiple value per field.
Example:
In the Sales Group field, If I put "01, 07" all the delivery from sales group 01 and 07 would appear in the table. Currently I am using LIKE condition.
Now my question is, how can I pass comma delimited values into my MS SQL stored procedure?
Maybe my approach is wrong. Is there other way to do this? Is it on MS Sql part or the PHP part?
Here's my sql query: (UPdate: Here is the final QUERY that solved my problem. Thanks PSA)
-- inventoryDetailed '0', '10', '1', '', '', '', '02/01/2018', '02/28/2018'
-- inventoryDetailed '0', '10', '1', NULL, NULL, NULL, '01/01/2018', '05/31/2018'
-- exec inventoryDetailed 0, 10, 1, NULL, NULL, NULL, '01/01/2018', '07/28/2018'
ALTER PROCEDURE [dbo].[inventoryDetailed]
@start int,
@pageSize int,
@userId bigint,
@salesGroupCode nvarchar(10),
@salesOfficeCode nvarchar(10),
@salesDistrictCode nvarchar(10),
@DtRcvFrom DATETIME,
@DtRcvTo DATETIME
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @storeSalesGroup nvarchar(max)
DECLARE @salesGroupTable TABLE
(
salesGroup nvarchar(10)
)
SET @storeSalesGroup = @salesGroupCode
while len(@salesGroupCode) > 0
begin
--exec YourSP left(@S, charindex(',', @S+',')-1)
insert into @salesGroupTable values(left(@salesGroupCode, charindex(',', @salesGroupCode +',')-1))
set @salesGroupCode = stuff(@salesGroupCode, 1, charindex(',', @salesGroupCode+','), '')
end
SELECT d.SGrp as salesGroupCode,
d.SGrpNm as salesGroup,
d.SOffc as salesOfficeCode,
d.SOffcNm as salesOffice,
d.SDst as salesDistrictCode,
d.SDstNm as salesDistrict,
a.CustCode as customerCode,
d.CustNm as customer,
c.MatrlCode as materialCode,
c.MatDesc as material,
b.Qty as qty,
b.ExpDt as batch,
datepart(wk, a.DtRcv) as week,
a.DtRcv as dateRcvd,
e.LName + ', ' + e.FName as diser,
overall_count = COUNT(*) OVER()
FROM [BigEMerchandiser].[dbo].[tbl_Inventory_H] as a
INNER JOIN [BigEMerchandiser].[dbo].[tbl_Inventory_D] as b
ON a.TransCtr = b.TransCtr
INNER JOIN BigEMasterData.dbo.tbl_Materials as c
ON b.Material = c.ExtMatGrp
INNER JOIN BigEMasterData.dbo.tbl_Customers as d
ON a.CustCode = d.CustCode
inner JOIN [BigESentData].[dbo].[tbl_sentRegistration] as e
on a.CreatedBy = e.CellNum
inner join BigEUsers.dbo.user_role_area as f
on d.SDst = f.area_id
inner join BigEUsers.dbo.users as g
on f.role_id = g.role_id
WHERE datepart(wk, a.DtRcv) BETWEEN datepart(wk, @DtRcvFrom) AND datepart(wk, @DtRcvTo)
AND (d.SGrp IN (SELECT salesGroup from @salesGroupTable))
OR (ISNULL(@storeSalesGroup,'')='')
AND d.SOffc LIKE ISNULL('%' + @salesOfficeCode + '%', d.SOffc)
AND d.SDst LIKE ISNULL('%' + @salesDistrictCode + '%', d.SDst)
AND g.id = @userId
ORDER BY a.DtRcv desc
OFFSET @start ROWS
FETCH NEXT @pageSize ROWS ONLY;
ENDAny tips and suggestion would be much appreciated. Thankyou so much.
EDIT:
Thanks PSK for the idea.
What I did is pass the string from PHP. Example: '01, 07'.
Then, in MS SQL, I created a table variable.
DECLARE @salesGroupTable TABLE
(
salesGroup nvarchar(10)
)
Next, I will get split the string passed from PHP ('01, 07') and then insert it to the table variable.
Next, I will use that table variable in my IN condition.
WHERE d.SGrp IN (SELECT salesGroup from @salesGroupTable)
Now, it is working fine. It is working as expected.
But if the user didn't input anything, I must return all the results. Hope you can help me with this. Thanks!
