Building a Query dynamically inside a Sybase Store Procedure

Hi ,
I have a requirement like this . I have some 10 conditions in the where clause . And if some arguments are null , then the condition is not to be included in the where condition.
I can write a if else condition to build the query . But it will be different queries.
 If ( condition)
    some sql query
 else if ( some other condition)
    some other sql .
Is it possible to build the query dynamically and execute the query in the end like I do in java ( where a single query does the job and depending on the condition I add or don't add the where clause - I don't write a separate query)
Can the same be done inside a Sybase store proc and how?
Any reply will be appreciated?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Joe WoodhousePrincipal ConsultantCommented:
You can build dynamic SQL in ASE 12 or above.



SELECT @sql = "SELECT [...] FROM [...] WHERE 1=1"

IF  [condition 1]
   SELECT @sql = @sql + "AND  [where clause 1]"

IF  [condition 2]
   SELECT @sql = @sql + "AND  [where clause 2]"


By putting in the dummy WHERE clause in the first SELECT, you don't have to worry about whether subsequent WHERE clauses are the first clause or not. If the entire query looks to be larger than 255 characters, you can easily break this up, perhaps by making everything up to "WHERE 1 = 1" into the first variable, and everything else into one or more, which you then concatenate together.

You call this with:

exec (@sql)

This only works in ASE 12 or above.

Note that there are issues with reuse of procedure query plans if you go down this path - a query plan could be generated that was appropriate for one procedure call but not the fastest way of executing the next - but if the first procedure's query plan is idle in procedure cache, then it will (inappropriately) be reused. If that query plan contained a table scan you don't need, you're in trouble.

The standard answer to this is to CREATE PROC [...] WITH RECOMPILE, but I prefer to create it normally and instead EXEC PROC [...] WITH RECOMPILE which at least allows for the possibility of reusing a query plan if and when you can safely do so.

If you're running ASE 11.9.2 or below, firstly you should update since it hasn't been supported by Sybase for years.

Beyond that, you could have code external to Sybase (shell script? batch file? java?) that dynamically creates the procedure for you, then calls it - but if you can do that, you may as well just create it as regular SQL and run it that way.

I think your original approach is in fact the best one. Have conditional logic that calls different subprocedures depending on what you want to do. This makes the outermost procedure nothing but a launchpad for other procs, and each subprocedure will always have the same query plan. You won't have any issues with reusing a bad plan this way. I typically rewrite what you want to do into what you're already doing, and have seen some significant performance gains (5 mins -> 0.05 seconds, for example). 8->

Good luck!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jan FranekCommented:

may be your problem can be solved also without dynamic SQL:

declare @par1 int, @par2 int

select *
from your_table
where (par1 = @par1 or @par1 is null)
and (par2 = @par2 or @par2 is null)

In this simle example, if you set @par1 or @par2 to NULL, the query behaves like there's no condition on this parameter.

However, you must check is this approach can be used in your situation (if it's just ommiting null parameter's it should be OK) and I recommend to be careful with large tables as I'm not sure if Sybase server can find a reasonable query plan.
The ORs might your performance.
You can do it wothout using the ISNULL function

WHERE Col1 = ISNULL(@Arg1, Col1)
AND Col2 = ISNULL(@Arg2, Col2)
AND Col3 = ISNULL(@Arg3, Col2)
AND Col4 = ISNULL(@Arg4, Col2)

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.