[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 799
  • Last Modified:

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?
1 Solution
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!
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)


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now