locked
Searching for column values based on dynamic string RRS feed

  • Question

  • User136609187 posted

    Need a help on searching a specific column in sqlserver with multiple values which is being selected dynamically from front end

    Multiple values with delimiter will be passed as parameter to stored procedure -  eg (@Product = Acer, HP, Lenovo)

    I tried the below one but its not working. If no products are selected means if @Product =null then it should return all the values.

    Tried using the In operator but in case of null(@Product =null)  it will not return any result set

    select * from tblProductDetails where ProductName like ['ACER%','HP%','Lenovo%']
    select * from tblProductDetails where ProductName in['ACER','HP','Lenovo'] - works but if null is passed will not return any rows

    Is there any better way that i can do this using any other method..

    Please help

    Thursday, January 21, 2016 4:46 AM

Answers

  • User-1716253493 posted
    select * from tblProductDetails where ProductName in['ACER','HP','Lenovo'] OR @product is Null

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 21, 2016 7:03 AM
  • User-219423983 posted

    Hi chithra_iyer,

    I have created a SP as below to pass the value of “In” and then get the result.

    Alter PROCEDURE [dbo].MyProc
    	@searchqry varchar(50)
    AS
    	declare @querstr nvarchar(1000)
    	if @searchqry is not null
    		begin
    			set @querstr = N'select * from Products where ProductName in ('+@searchqry+')'
    		end
    	else 
    		begin
    			set @querstr = N'select * from Products'
    		end
    	exec (@querstr)
    RETURN 0
    

    When you want to call the SP, you could use the following code to call the SP. I have tested it on my client and it works.

                using (SqlConnection conn = new SqlConnection(strConnString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("[dbo].[MyProc]", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter sp = new SqlParameter("@searchqry", SqlDbType.VarChar);
                    sp.Direction = System.Data.ParameterDirection.Input;
                    sp.Value = "'Tofu','Chang'";//Here, you could add "'" to the values
                    cmd.Parameters.Add(sp);
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        DataTable dt = new DataTable();
                        sda.Fill(dt);
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            //Do what you want
                        }
                    } 
                }
    

    But, if you want to pass the value of “In” parameter, you’d better use the Table-Valued Parameters to achieve this need instead of passing “In” values to avoid the “SQL injection”. Besides, the Table Valued Parameters could define the incoming values, when you pass “In” values, you need to consider the passing value data type. As usual, combining the SQL query sting is not a good practice.

    Best Regards,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 4, 2016 10:06 AM

All replies

  • User177399542 posted

    Hi Chitra_Iyer Donot pass NULL pass empty string instead. 

    select * from tblProductDetails where ProductName like '%%'

    Thursday, January 21, 2016 6:31 AM
  • User-1716253493 posted
    select * from tblProductDetails where ProductName in['ACER','HP','Lenovo'] OR @product is Null

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 21, 2016 7:03 AM
  • User-698989805 posted

    Well!! This worked in my project and implemented with CheckBox. Here is a sample:

    SELECT * FROM Products WHERE ProductName IN ('Levis','Zara') OR ProductName IS NULL

    Thursday, January 21, 2016 7:18 AM
  • User136609187 posted

    Thanks for reply !! It works fine but one issue

    declare @searchqry varchar(50)
    set @searchqry = '''ACER'',''HP'''
    print @searchqry

    select * from tblProductDetails where ProductName in (@searchqry) or @searchqry is Null

    When i put the search string directly with IN statement its working fine ('ACER','HP')
    but when its set to variable (set @searchqry = '''ACER'',''HP''') its returning empty rows but when i print
    @searchqry its exactly giving the 'ACER','HP'.
    Not sure whats wrong when i add variable instead of search string directly.
    Please help

    Thursday, January 21, 2016 11:38 AM
  • User-698989805 posted

    I think, you are trying to show the print value with C#. If so, then you have to use InfoMessage event. See the following link:

    http://stackoverflow.com/questions/1880471/capture-stored-procedure-print-output-in-net

    Hope it helps.

    Thursday, January 21, 2016 1:13 PM
  • User-219423983 posted

    Hi chithra_iyer,

    I have created a SP as below to pass the value of “In” and then get the result.

    Alter PROCEDURE [dbo].MyProc
    	@searchqry varchar(50)
    AS
    	declare @querstr nvarchar(1000)
    	if @searchqry is not null
    		begin
    			set @querstr = N'select * from Products where ProductName in ('+@searchqry+')'
    		end
    	else 
    		begin
    			set @querstr = N'select * from Products'
    		end
    	exec (@querstr)
    RETURN 0
    

    When you want to call the SP, you could use the following code to call the SP. I have tested it on my client and it works.

                using (SqlConnection conn = new SqlConnection(strConnString))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("[dbo].[MyProc]", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter sp = new SqlParameter("@searchqry", SqlDbType.VarChar);
                    sp.Direction = System.Data.ParameterDirection.Input;
                    sp.Value = "'Tofu','Chang'";//Here, you could add "'" to the values
                    cmd.Parameters.Add(sp);
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        DataTable dt = new DataTable();
                        sda.Fill(dt);
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            //Do what you want
                        }
                    } 
                }
    

    But, if you want to pass the value of “In” parameter, you’d better use the Table-Valued Parameters to achieve this need instead of passing “In” values to avoid the “SQL injection”. Besides, the Table Valued Parameters could define the incoming values, when you pass “In” values, you need to consider the passing value data type. As usual, combining the SQL query sting is not a good practice.

    Best Regards,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 4, 2016 10:06 AM
  • User-62323503 posted

    Thanks for reply !! It works fine but one issue

    declare @searchqry varchar(50)
    set @searchqry = '''ACER'',''HP'''
    print @searchqry

    select * from tblProductDetails where ProductName in (@searchqry) or @searchqry is Null

    When i put the search string directly with IN statement its working fine ('ACER','HP')
    but when its set to variable (set @searchqry = '''ACER'',''HP''') its returning empty rows but when i print
    @searchqry its exactly giving the 'ACER','HP'.
    Not sure whats wrong when i add variable instead of search string directly.
    Please help

    Refer below post to understand how to use variable in "IN" clause in SQL Server

    http://www.itdeveloperzone.com/2013/03/using-variable-in-in-clause-in-sql.html

    Saturday, March 5, 2016 12:13 PM