InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 00:25:02 GMT20http://www.sqlservercentral.com/Forums/Topic1369651-1292-1.aspx[quote][b]shilpaprele (10/8/2012)[/b][hr]I have one procedure with parameter @P. If @p= null & table abc has many rows with col1 = null, then following select does not reurn the values select *from abcwhere col1 = @Pwhat should be correct the syntax( do not wan tto use set ansi null on/off).[/quote]The correct way, to return records, would be:[code="sql"]select *from abcwhere col1 = @P OR (@P IS NULL AND col1 IS NULL)[/code]Using, ISNULL function on a column would stop this query to SARGable if you have an index on Col1Saying all the above, you should really read Gail Shaw's paper on catch-all-queries :[url]http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]Mon, 08 Oct 2012 07:24:49 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1369651-1292-1.aspxHi, sorry I forget about passing parameters..chk this one, hope this one helps -IF (@p = 'NULL' OR @p IS NULL)BEGIN SELECT * FROM abc WHERE name1 IS NULLENDELSEBEGIN SELECT * FROM abc WHERE name1 LIKE @pENDMon, 08 Oct 2012 05:52:14 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1369651-1292-1.aspxDear Valued memberthis is not taking care of the null value in parameterMon, 08 Oct 2012 05:16:17 GMTKrishna1http://www.sqlservercentral.com/Forums/Topic1369651-1292-1.aspxselect * from abcwhere column_name IS NULLMon, 08 Oct 2012 04:48:37 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1369651-1292-1.aspxCheck this:select *from abcwhere ISNUll(col1,'') = ISNULL(@P,'')Mon, 08 Oct 2012 02:42:08 GMTBhumika Jawanjalhttp://www.sqlservercentral.com/Forums/Topic1369651-1292-1.aspx[quote][b]shilpaprele (10/8/2012)[/b][hr]I have one procedure with parameter @P. If @p= null & table abc has many rows with col1 = null, then following select does not reurn the values select *from abcwhere col1 = @Pwhat should be correct the syntax( do not wan tto use set ansi null on/off).[/quote][code="sql"]select *from abcwhere col1 = @P OR (@P IS NULL AND col1 IS NULL)[/code]Mon, 08 Oct 2012 02:37:33 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1369651-1292-1.aspxI have one procedure with parameter @P. If @p= null & table abc has many rows with col1 = null, then following select does not reurn the values select *from abcwhere col1 = @Pwhat should be correct the syntax( do not wan tto use set ansi null on/off).Mon, 08 Oct 2012 01:45:34 GMTKrishna1