Have Geek Brain

Get all or partial result from sql using one TSQL code

Here is my condition. There is a textbox in the form, if you don't enter anything, it will return all the rows in this table. If you enter something, it will return rows whose Col1 matches the input. I am trying to use the sql below to do this. But there is one problem, these columns are nullable. It will not return a NULL string. Is there a way to return all or matched rows based on input?

Update

I am using ObjectDataSource and ControlParameter to pass parameter, when control input is empty, ObjectDataSource object passes DBNULL to TSQL compilation.

Col1   Col2   Col3
ABCD   EDFR   NULL
NULL   YUYY   TTTT
NULL   KKKK   DDDD


select * from TABLE where Col1 like Coalesce('%'+@Col1Val+'%',[Col1])
+2


a source to share


4 answers


You tried



SELECT   * 
FROM     TABLE 
WHERE    COALESCE(Col1, '') LIKE COALESCE ('%'+@Col1Val+'%', [Col1])
0


a source


Something like this might work.



select * from TABLE where Coalesce(Col1,'xxx') like Coalesce('%'+@Col1Val+'%',Col1, 'xxx')
0


a source 


SELECT * FROM [TABLE]
WHERE (Col1 LIKE '%'+@Col1Val+'%' OR (@Col1Val = '' AND Col1 IS NULL))
0


a source


Use this:

SELECT   * 
FROM     TABLE 
WHERE    
    ( @Col1Value IS NOT NULL AND COALESCE(Col1, '') LIKE '%' + @Col1Val +'%' )
    OR @Col1Value IS NULL

Or perhaps use this so that null won't creep into your request:

string nullFilteredOutFromQueryString = @"SELECT   * 
FROM     TABLE 
WHERE    
    ( @Col1Value <> '' AND COALESCE(Col1, '') LIKE '%' + @Col1Val +'%' )
    OR @Col1Value = ''";


var da = new SqlDataAdapater(nullFilteredOutFromQueryString, c);
// add the TextBox Text property directly to parameter value.
// this way, null won't creep in to your query
da.SelectCommand.Parameters.AddWithValue("Col1Value", txtBox1.Text); 
da.Fill(ds, "tbl");
0


a source






More articles:


All Articles