Search code examples
sqlodbcsqlparameter

SQL Parameters - where does expansion happens

I'm getting a little confused about using parameters with SQL queries, and seeing some things that I can't immediately explain, so I'm just after some background info at this point.

First, is there a standard format for parameter names in queries, or is this database/middleware dependent ? I've seen both this:-

DELETE * FROM @tablename

and...

DELETE * FROM :tablename

Second - where (typically) does the parameter replacement happen? Are parameters replaced/expanded before the query is sent to the database, or does the database receive params and query separately, and perform the expansion itself?

Just as background, I'm using the DevArt UniDAC toolkit from a C++Builder app to connect via ODBC to an Excel spreadsheet. I know this is almost pessimal in a few ways... (I'm trying to understand why a particular command works only when it doesn't use parameters)

Solution

  • SQL parameters are sent to the database. The database performs the expansion itself. That allows the database to set up a query plan that will work for different values of the parameters.

    Microsoft always uses @parname for parameters. Oracle uses :parname. Other databases are different.

    No database I know of allows you to specify the table name as a parameter. You have to expand that client side, like:

    command.CommandText = string.Format("DELETE FROM {0}", tableName);

    P.S. A * is not allowed after a DELETE. After all, you can only delete whole rows, not a set of columns.