Dynamic Query... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic Query...

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-05-15 : 08:45:40

manoj writes "wanna write Dynamic Select Query to fetch data from a single data table by checking if any of column value passed in 'where' is null or not."

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-15 : 08:56:33

Create procedure DynamicSP (para varchar(100))
AS
BEGIN

Declare @s varchar(1000), @Cond
if para is Null
set @Cond = 'is Null'
else
set @Cond = 'is Not Null'

set @s = 'Select count(*) from MyTbl where Field ' + @Cond
Exec (@s)

END

Srinika

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-15 : 09:17:00

You might not required Dynamic SQL at all
use coalesce

select *
from   yourtable
where  col1 = coalesce(@col1, col1)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-15 : 09:32:31

If you want to get all records from table if the passed value is NULL, then use Tan's method

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-15 : 10:42:56

Note that if col1 (in Tan's example) is nullable, you might need to a slight modification. See this for example...

--data
declare @yourtable table (col1 int)
insert @yourtable
          select 1
union all select 2
union all select null
union all select null
union all select 2

--calculation
declare @col1 int

--Tan's example (nulls are never returned)
set @col1 = null
select * from @yourtable where col1 = coalesce(@col1, col1)

set @col1 = 2
select * from @yourtable where col1 = coalesce(@col1, col1)

--One alternative approach (nulls can be returned)
set @col1 = null
select * from @yourtable where @col1 is null or col1 = @col1

set @col1 = 2
select * from @yourtable where @col1 is null or col1 = @col1

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources