[freetds] Using sp_executesql with Microsoft SQL Server 2000

Rogers, Tom tom.rogers at ccur.com
Mon Jun 30 12:35:42 EDT 2003

Recently we had issues with the current FreeTDS implementation working with
UnixODBC in that we were not getting stored procedure output parameters
returned.  The issues surrounding output parameters and TDS and how
Microsoft changed things around SQL 7.0 timeframe are pretty well documented
in the archive so I won't digress.

However, to answer your specific question dealing with the use of
sp_executesql for parameter passing, though I think on the surface it is a
good idea for the reasons you state, just be aware that I don't think said
system SP supports output parameters; at least last time I tried I couldn't
get them to work.  It seems that the parameters that the system SP take are
only for input. So, that could be a limitation, depending on your needs.

Thomas Rogers
System Engineer
Concurrent Computer Corporation
> phone: 215.712.7422 x7261

-----Original Message-----
From: Staats, Frederick W [mailto:frederick.w.staats at intel.com]
Sent: Monday, June 30, 2003 11:47 AM
To: freetds at lists.ibiblio.org
Subject: [freetds] Using sp_executesql with Microsoft SQL Server 2000

Has anyone on the freeTDS team looked at using sp_executesql in
freeTDS for parameter passing using freeTDS with Microsoft SQL
Server 2000?

It turns out that Microsoft SQL 2000 internally uses a system
stored procedure called sp_executesql for runtime bound SQL
statements (both the OLE DB and ODBC Libs use this internally.)
An example statement looks like:

EXECUTE sp_executesql
 N'INSERT INTO odbctest.odbctest.test (foo, bar) VALUES (@key1, @key2)',
 N'@key1 INT, @key2 TEXT',
 @key1 = 1,
 @key2 = 'abc'

Where the first argument is the SQL to execute, the second is
the types of the arguments and the third are the local
temporary values to assign (they are privately scoped inside
sp_executesql stored procedure.

The advantage of this procedure is that the text of the embedded
SQL statement matches exactly every time speeding up the
optimization engine in the SQL server.  (No fancy pattern
matching needed to determine if it can reuse an execution plan.)

The only real limit I've seen in the docs so far is that all objects
(TABLES, VIEWS, etc.)  must be fully qualified (eg.
database.owner.table) for the optimization engine to match using

I'm working on an optional perl only extension to DBD::Sybase
that will allow me to do '?' based parameter passing using
this technique, but am wondering if anyone has looked a
lower level interface.

Frederick (Fritz) Staats
frederick.w.staats at intel.com
FreeTDS mailing list
FreeTDS at lists.ibiblio.org

More information about the FreeTDS mailing list