Building a Query dynamically inside a Sybase Store Procedure

Posted on 2004-11-04
Last Modified: 2010-08-05
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?
Question by:moonoo
    LVL 24

    Accepted Solution

    You can build dynamic SQL in ASE 12 or above.


    DECLARE @sql VARCHAR(255)

    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!
    LVL 14

    Expert Comment


    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.
    LVL 5

    Expert Comment

    The ORs might your performance.
    You can do it wothout using the ISNULL function

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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    If you're not part of the solution, you're part of the problem.   Tips on how to secure IoT devices, even the dumbest ones, so they can't be used as part of a DDoS botnet.  Use PRTG Network Monitor as one of the building blocks, to detect unusual…
    Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now