[Solved] Dynamic Sql/Where - 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 2005 Forums

Transact-SQL (2005)

[Solved] Dynamic Sql/Where

Author

Topic

CVDpr
Starting Member

41 Posts

Posted - 2008-09-15 : 13:43:39

Hey there, is there away to create a runtime select *from ....where?

i have a form that the user can search the database depending of the selected option.

if the user select CDMID then is select ....where CDMID = @CDMID.
if the user select InsID then is select.... where InsID =@InsID
if the user select CDMID and InsID then select .... where CDMID=@CDMID and InsID=@InsID.
etc..... is like 3 to 5 options.


create procedure sp_sel_MstAIBISA_Search 

@CDMId as int,
@InsId as int


as
begin
		
		if @CDMId > 0  AND @InsId = 0
			BEGIN
				select CDMId, InsId, EffectiveDateFrom,	ServiceArea, CPT, Modifier1, Modifier2, Modifier3, Modifier4
				from AppInfo with(nolock)	where CDMId = @CDMId
			END
		else if @InsId > 0  and @CDMId = 0
			begin
				select CDMId, InsId, EffectiveDateFrom,	ServiceArea, CPT, Modifier1, Modifier2, Modifier3, Modifier4
				from AppInfo with(nolock)	where InsId = @InsId
			end
		else
			begin
				select CDMId, InsId, EffectiveDateFrom,	ServiceArea, CPT, Modifier1, Modifier2, Modifier3, Modifier4
				from AppInfo with(nolock)	where CDMId = @CDMId and InsId = @InsId
			end	
end

and this is only 2 options, ca you imagine is the user select option 1 and 5 :-o?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 13:47:14

[code]create procedure sp_sel_MstAIBISA_Search

@CDMId as int=-1,
@InsId as int=-1

as
begin

select CDMId, InsId, EffectiveDateFrom, ServiceArea, CPT, Modifier1, Modifier2, Modifier3, Modifier4
from AppInfo with(nolock)
where (CDMId = @CDMId or @CDMId=-1)
AND (InsId = @InsId or @InsId=-1)
... (for other options)
end
go[/code]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-15 : 13:48:09

Using dynamic SQL for a scenario where 3-5 options are possible is poor programming. Dynamic SQL is bad for performance and security reasons.

You can achieve a dynamic WHERE clause using the COALESCE trick. Here's the article: http://www.sqlteam.com/article/implementing-a-dynamic-where-clause

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-15 : 13:53:05

visakh16 has a good solution...... learning and using boolean logic is important to know when working with databases and computer programming in general.

- Jeff
http://weblogs.sqlteam.com/JeffS

CVDpr
Starting Member

41 Posts

Posted - 2008-09-15 : 14:15:28

quote:
Originally posted by tkizer

Using dynamic SQL for a scenario where 3-5 options are possible is poor programming. Dynamic SQL is bad for performance and security reasons.

You can achieve a dynamic WHERE clause using the COALESCE trick. Here's the article: http://www.sqlteam.com/article/implementing-a-dynamic-where-clause

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

and

quote:

Originally posted by visakh16

create procedure sp_sel_MstAIBISA_Search 

@CDMId as int=-1,
@InsId as int=-1
as
begin
		
				select CDMId, InsId, EffectiveDateFrom,	ServiceArea, CPT, Modifier1, Modifier2, Modifier3, Modifier4
from AppInfo with(nolock)	
where (CDMId = @CDMId or @CDMId=-1)
AND (InsId = @InsId or @InsId=-1)
... (for other options)			
end
go

And know the Question, wich one of both is the better way?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-15 : 14:52:07

As I already suggested, I prefer visakh16's solution since it uses simple, clean boolean logic, which as I already wrote, is important to understand when programming.

- Jeff
http://weblogs.sqlteam.com/JeffS

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-15 : 14:53:26

If you are asking about performance, I suspect performance will be identical so go with whichever you way you feel more comfortable. I'd compare execution plans first though to be sure.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

CVDpr
Starting Member

41 Posts

Posted - 2008-09-15 : 15:33:47

ok thnks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-16 : 03:40:26

Erland covered many possible methods
http://www.sommarskog.se/dyn-search.html

Madhivanan

Failing to plan is Planning to fail

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-09-16 : 04:43:44

select CDMId, InsId, EffectiveDateFrom, ServiceArea, CPT, Modifier1, Modifier2, Modifier3, Modifier4
from AppInfo with(nolock)

where CDMId = case when @CDMID = 0 then CDMId else @CDMID
and
InsId= case when @InsID = 0 then InsId else @InsID

this is another approach

CVDpr
Starting Member

41 Posts

Posted - 2008-09-18 : 14:56:46

THis is working great


@CDMId as int=-1,
@InsId as int=-1,
@ServArea as varchar(10)
as
begin	
	select CompanyId, FacilityId, CDMId, InsId, EffectiveDateFrom,	ServiceArea, CPT, Modifier1, Modifier2, Modifier3, Modifier4
	from appinformationofbillingbyinsuranceandservicearea with(nolock)	
	where (CDMId = @CDMId or @CDMId = -1)
	and (InsId = @InsId or @InsId = -1)
	and (ServiceArea = @ServArea or @ServArea = '')	order by CDMId
end

but when i run this to see all the records it takes 30 Seconds!
sp_sel_MstAIBISA_Search -1,-1,''

why it takes 30 seconds?
but if i run this:


select *from  appinformationofbillingbyinsuranceandservicearea with(nolock)

only takes 5 seconds.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-19 : 00:24:59

quote:
Originally posted by CVDpr

THis is working great

@CDMId as int=-1,
@InsId as int=-1,
@ServArea as varchar(10)
as
begin
select CompanyId, FacilityId, CDMId, InsId, EffectiveDateFrom, ServiceArea, CPT, Modifier1, Modifier2, Modifier3, Modifier4
from appinformationofbillingbyinsuranceandservicearea with(nolock)
where (CDMId = @CDMId or @CDMId = -1)
and (InsId = @InsId or @InsId = -1)
and (ServiceArea = @ServArea or @ServArea = '') order by CDMId
end

but when i run this to see all the records it takes 30 Seconds!
sp_sel_MstAIBISA_Search -1,-1,''

why it takes 30 seconds?
but if i run this:

select *from appinformationofbillingbyinsuranceandservicearea with(nolock)

only takes 5 seconds.

have a look at execution plan while executing the procedure to see costly steps.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources