Dynamic query generator - 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 generator

Author

Topic

Zim327
Yak Posting Veteran

62 Posts

Posted - 2009-09-17 : 13:46:58

I created this dynamic sql sp as a test.


Alter PROCEDURE spDynCon   
	@ContractNum		varchar(50) = null, 
	@PoPStart			datetime = null,			
	@PoPEnd				datetime = null
AS
BEGIN
	SET NOCOUNT ON;
select ConID, ProgramID, ProjID, ContractNum, PoPStart, PoPEnd from dbo.Contracts
where	ContractNum like coalesce('%' + @ContractNum + '%', '%') and
		PoPStart = coalesce(PoPStart, @PoPStart) and
		PoPEnd = coalesce(PoPEnd, @PoPEnd)
END
GO

this seems to work (even though ContractNum is not nullable)
However, the real question is how can I dynamically change the operators (i.e. like, not like, =, <, >, <=, >=, <>) ??
I was thinking about passing an array of operators (from a web form) but with the final engine going to have say 20 potential parameters + the operators. arg! that's a lot of parameters
Is there an easier way to do this?
Thanks,

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-17 : 14:06:31

What you've posted is not actually dynamic sql. It is simply a parameterized query. For Search SPs that can have many nullable parameters with different possible operators, real dynamic sql becomes a viable option. When I say dynamic sql I mean where you construct a string that is a valid statement and is then "executed" by either using EXEC(<string>) or by calling the SP: sp_executeSQL. The advantage here is that for any given call the resulting statement may be MUCH simpler than a big static statement that takes all possibilities into account. This is especially true if some of your parameters being NULL means that you can leave tables and JOINs out of the where clause. The SP is still quite complicated looking - if not down right ugly - but the resulting statement that is sent to the server can be quite small and efficient.

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-18 : 02:12:20

www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources