We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now


Need sample code for using Sybase Dynamic SQL, esp. with multiple parameters

minjiezen asked
Medium Priority
Last Modified: 2012-05-06
Where can I find sample code for preparing a statement, set the input parameter values, and execute the statement using ct_dynamic function? Especially when there are multiple input parameters to set (bind)? I have only found sample code for setting ONE parameter.
Watch Question

You are probably looking at the "dynamic2.cp" code.

Just keep adding substitution question marks and comma separated bind variables in the using clause.


		"select *
                   from publishers
                  where pub_id = ?
                    and pub_state = ?";
EXEC SQL EXECUTE sel_stmt INTO :pub_id, :pub_name, :pub_city, :pub_state
                         USING :pub_id, :pub_state ;

Open in new window


Hi Bill, thanks for the fast response. Sorry for not stating it clearly. I'm actually trying to do do dynamic SQL  in C++ with Sybase Client Library, with the library APIs like ct_dynamic(), ct_send(), ct_param(), ct_results().

O.K., I think this is the hardest way possible to do Client Lib programming.

To do prepared statements, you have to:
  ct_dynamic : prepare
  ct_dynamic : describe input
  ct_dyndesc  (which will give you the description of the input parameters)
  Loop through the ct_dyndesc data structures can call ct_param for each one
  ct_dynamic : execute
  ct_dynamic : describe output
and then you can process the results of the SQL statement.

The deal on dynamic SQL is this:  If it is truly dynamic, it is a waste of time preparing statements.  Just build your complete SQL statement with the parameters already substituted in the string and do a ct_dynamic : execute_immediate.  When you prepare a "dynamic" SQL statement, you are actually creating a temporary stored procedure under the covers at run time that lives only for the life of the connection.  Embedded SQL does the same thing because it uses these calls under the covers.

So you can see, if you are doing something like building a general purpose query interface in an application and you truly cannot predict what the user will ask for, you really have dynamic SQL and you might as well just build the entire statement once and be done.  All the overhead and complexity of preparing the statement is wasted since the SP will never get used again and you cannot assume what the input parameters and results set will look like so you have to code it all dynamically.

If, on the other hand, you actually have fairly static SQL statements, your best bet is to code them into stored procedures yourself and then call them as RPCs (not exec immediate command strings).  This is by far the lowest overhead method and gives you other advantages such as keeping the SQL in the database, allowing you to tune the queries, provides better performance, gives you code reuse, simplifies the C++ code, etc.

Stored procedures also gives you the option of assuming the parameter set so you don't have to deal with getting the description of the input parameters.
 In many cases, people resort to dynamic SQL not because the result set changes from call to call but because the WHERE clause changes.  Obviously, you can pass parameters to a bunch of fields appearing the WHERE clause but you might want to limit it to just one or two of several.  There are a couple of tricks for doing this.  See the code snippet below.

There are other techniques for dealing with lists of values and IN clauses as well.  The bottom line is to avoid putting SQL in your C++ code at all costs.  It is much more difficult to create, debug, maintain, and impossible to tune.  Another advantage of dividing things up is that there is a natural division of labor as well.  One person can wrtie C++ while another works on stored procedures.

I don't know which situation you are in but I am pretty sure that prepared statements are the last way you want to try approaching the problem.


create procedure fubar @a int, @b int, @c int
  select *
    from barfu bf
   where (bf.a = @a or @a is null)
     and (bf.b = @b or @b is null)
     and (bf.c = @c or @c is null)

Open in new window


  Bill, thanks a lot for the detailed info. I really appreciate it.  
   The purpose of using dynamic SQL is to prevent SQL injection attacks -- to parameterize the SQL statements. In this case I'm only left the choices of prepare-and-execute method or stored procedure.
   According to what you stated, should I convert all the dynamic SQL statements into stored procedures (SPs),  use ct_param() to set the input parameters, and then use ct_command() calls with CS_RPC_CMD option to call those SPs, instead of using ct_dynamic calls with CS_PREPARE and CS_EXECUTE options? There are quite a few dynamic SQL statements in the application. Do you think it would affect the database performance by creating many stored procedures like this?
   Thanks again.
Yes.  You should definitely convert all the dynamic SQL to stored procedures and use ct_param and ct_command with CS_RPC_CMD.

Yes, database performance will be affected by converting to stored procedures; it will get better!  The number of procedures is irrelevant to database performance.  Each one represents pre-parsed, pre-optimized, and pre-compiled code; all steps that dynamic SQL has to do each time you run it.  Prepared statements are worse if you are throwing them away each time and not quite as bad as just passing SQL strings if you are reusing them quite a bit.

Since you are worried about application security, the use of stored procedures tightens things up even further.  For example, you can grant the application user no rights at all except EXEC on the stored procedures you want him/her to use.  The stored procedure owner has to have rights to the tables when you compile them.  SQL Injection is not a problem at all because you are doing everything as RPC calls and nobody (except DBO) needs to have access to the actual data tables.

If you need to grant table level access to certain users, say internal people running a reporting tool against the same database, you can still do so as long as you keep the user ids distinct.  You can limit that access to read-only unless there is a need for read-write access.  Some sites take all this a step further and build everything on top of views that have a layer of security built in them as well.

Creating all those stored procedures is not a bad job if you have a database IDE to work with.  Sybase Workspace is free, either thru Sybase or via membership in the ISUG ($95 well spent.)  You can also use Embaracdero RapidSQL Pro or Aquafold Aqua Data Studio.  There are some others out there as well but those are the three biggies.  All three have interactive debuggers for T-SQL procedures and triggers as well so you should be in much better shape than you are now.

You can set up an assembly line for the procs.  Just clip the SQL out of the C++ code and drop it into a CREATE PROCEDURE template.  Give the procedure and the arguments names, put the argument list at the top, and you are done.


Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


Bill, thank you very much for all the insights.  
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.