[freetds] Using sp_executesql with Microsoft SQL Server 2000

Staats, Frederick W frederick.w.staats at intel.com
Mon Jun 30 11:46:40 EDT 2003


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
sp_executesql.

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
 



More information about the FreeTDS mailing list