Comma Delimited String as Parameter in T-SQL

[draven07]

Need some help here.

I was trying to make a Stored Procedure in MS SQL 2008 which will make use of comma delimited string as Parameter in my WHERE [COLUMN] LIKE [PARAMETER] condition.

Somehow, I was able to select rows of data from my DB table that matches the set of values I passed to the parameter however, Im not getting the ideal output from it.

Supposed I have a product 'BOLT & CAP ORANGE' and 'BOLT & CAP RED' in my PRODUCTS Table. Using C# as front end, if I typed "BOLT, ORANGE" in the search field I provided on my windows form, both products are being displayed. But what I need is that, whenever I type a string separated by comma in the search field, ONLY those products that has a "BOLT" and "ORANGE" words should be displayed, 'BOLT & CAP ORANGE' for instance.

Is this possible?

Supposed I have a product 'BOLT & CAP ORANGE' and 'BOLT & CAP RED' in my PRODUCTS Table. Using C# as front end, if I typed "BOLT, ORANGE" in the search field I provided on my windows form, both products are being displayed. But what I need is that, whenever I type a string separated by comma in the search field, ONLY those products that has a "BOLT" and "ORANGE" words should be displayed, 'BOLT & CAP ORANGE' for instance.

Have you created a query yet? If you did, post it. Right now, there's nothing to work on because there's no query nor code it's just explanation.

[draven07]

Here is my STORED PROC QUERY:

SELECT DISTINCT P.Name AS 'ProductName', 
       I.QuantityOnHand,
       SOL.UnitPrice,
       D.Name AS 'DiscountScheme', 
       I.ProductId
FROM BASE_Inventory I
    INNER JOIN BASE_Product P
        ON I.ProductId = P.ProductId
    INNER JOIN SO_SalesOrder_Line SOL
        ON P.ProductId = SOL.ProductId
    INNER JOIN BASE_DiscountScheme D
        ON D.DiscountSchemeId = SOL.DiscountSchemeId
    INNER JOIN BASE_Category Cat
        ON P.CategoryId = Cat.CategoryId
WHERE P.Name LIKE '%'+ @ProductName +'%' 
    AND P.CategoryId = @CategoryId AND P.IsActive = 1

and here is the code I used on my windows form:

            _searchText = txtSearchText.Text;
            string[] productNameList = _searchText.Split(',');
            string productName = "";
            int productNameListArrayCount = productNameList.Count() - 1, newDataGridRow;

            for (int i = 0; i <= productNameListArrayCount; i++)
                {
                         productName = productNameList[i];
                        _dsProductStocks = ProductDAL.getProductStocks(productName, _categoryId);

                        if (_dsProductStocks.Tables[0].Rows.Count > 0)
                        {
                            for (int j = 0; j < _dsProductStocks.Tables[0].Rows.Count; j++)
                            {
                                newDataGridRow = dtgProducts.Rows.Add();

                                dtgProducts.Rows[newDataGridRow].Cells["Stocks"].Value = _dsProductStocks.Tables[0].Rows[j]["QuantityOnHand"].ToString();
                                dtgProducts.Rows[newDataGridRow].Cells["ProductName"].Value = _dsProductStocks.Tables[0].Rows[j]["ProductName"].ToString();
                                dtgProducts.Rows[newDataGridRow].Cells["UnitPrice"].Value = _dsProductStocks.Tables[0].Rows[j]["UnitPrice"].ToString();
                                dtgProducts.Rows[newDataGridRow].Cells["DiscountScheme"].Value = _dsProductStocks.Tables[0].Rows[j]["DiscountScheme"].ToString();
                                dtgProducts.Rows[newDataGridRow].Cells["ProductId"].Value = _dsProductStocks.Tables[0].Rows[j]["ProductId"].ToString();

                            }
                        }
                }   

Maybe change/tweak this:

string[] productNameList = _searchText.Split(',');
string productName = "";
int productNameListArrayCount = productNameList.Count() - 1, newDataGridRow;

with this:

List<string> productNameList = new List<string>() {"BOLT", "CAP ORANGE", "CAP RED" };
string delimitedString = string.Join(", ", productNameList);

[draven07]

Well it doesn't provide me with the results I need. Using this, Im passing a value "BOLT, CAP ORANGE, CAP RED" into my stored procedure and it returns NULL since I have no "Product" that has a "BOLT, CAP ORANGE, CAP RED" name.

Well it doesn't provide me with the results I need. Using this, Im passing a value "BOLT, CAP ORANGE, CAP RED" into my stored procedure and it returns NULL since I have no "Product" that has a "BOLT, CAP ORANGE, CAP RED" name.

This method is old but still useful you can adjust your query to this and get that result:

http://www.4guysfromrolla.com/webtech/031004-1.shtml