I got to do a filter based on checkbox selected. I got 5 search criteria's where I just posted 2. I would select styles and leave solid_yarn empty. So the sql function CSVToTable based on selected values are not working properly?
if (style != null)
{
for (int i = 0; i < style.Length; i++)
{
parameterList.Append(style[i] + ",");
}
var parameters = parameterList.ToString().TrimEnd(',');
cmd.Parameters.AddWithValue("@Liststyle", parameters);
}
if (solid_yarn != null)
{
var parameterList1 = new StringBuilder();
for (int i = 0; i < solid_yarn.Length; i++)
{
parameterList1.Append(solid_yarn[i] + ",");
}
var parameters1 = parameterList1.ToString().TrimEnd(',');
cmd.Parameters.AddWithValue("@Listsolidyarn", parameters1);
}
My CSVToTable function
ALTER FUNCTION [dbo].[CSVToTableStyle] (@InStr VARCHAR(MAX))
RETURNS @TempTab TABLE
(Style varchar(MAX) not null)
AS
BEGIN
;
SET @InStr = REPLACE(@InStr + ',', ',,', ',')
DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @INSTR ) <> 0
BEGIN
SELECT @SP = PATINDEX('%,%',@INSTR)
SELECT @VALUE = LEFT(@INSTR , @SP - 1)
SELECT @INSTR = STUFF(@INSTR, 1, @SP, '')
INSERT INTO @TempTab(Style) VALUES (@VALUE)
END
RETURN
END
and My sql query in a stored procedure is as follows..
@Liststyle varchar(MAX)=null,
@Listsolidyarn varchar(MAX)=null,
select * from tbl_Pgm_Preview where (Brand=@brand) and (Department=@department) and (Season=@season) and (Group_Name=@groupname)
and (Style in ( Select * from dbo.[CSVToTableStyle](@Liststyle)))
and (Solid_yarn in ( Select * from dbo.[CSVToTableSolidYarn](@Listsolidyarn)))