MSSQL to MySQL: Dynamic SQL Parameter Substitution

[Peter Kierstead]

In MSSQL dynamic SQL statements can be executed via a system stored procedure call sp_ExecuteSQL. Example (minimally coded to highlight specific problem):

Declare @SQL nVarChar(8000),@FN VarChar(32),@MN VarChar(32),@LN VarChar(32);
Set @SQL=N'Select * from dbo.Table where FN=@FN and MN=@MN and LN=@LN';
Exec sp_ExecuteSQL @SQL
   ,N'@FN VarChar(32),@MN VarChar(32),@LN VarChar(32)'
   ,@FN,@MN,@LN;

Using this technique I can change the dynamic SQL statement's WHERE clause to use any combination of column filters, from one (or none, if I remove the WHERE clause completely) to all three, without having to change the calling signature of sp_ExecuteSQL.

Example 1 (notice change of WHERE clause):

Declare @SQL nVarChar(8000),@FN VarChar(32),@MN VarChar(32),@LN VarChar(32);
Set @SQL=N'Select * from dbo.Table where FN=@FN and LN=@LN';
Exec sp_ExecuteSQL @SQL
   ,N'@FN VarChar(32),@MN VarChar(32),@LN VarChar(32)'
   ,@FN,@MN,@LN;

Example 2 (notice change of WHERE clause):

Declare @SQL nVarChar(8000),@FN VarChar(32),@MN VarChar(32),@LN VarChar(32);
Set @SQL=N'Select * from dbo.Table where LN=@LN';
Exec sp_ExecuteSQL @SQL
   ,N'@FN VarChar(32),@MN VarChar(32),@LN VarChar(32)'
   ,@FN,@MN,@LN;

The parameter substitution takes place more like a function call than a one-to-one parameter to '?' substitution, as it appears that MySQL does. I don't have to change the signature of the dynamic execution call based on how many @ variables I use in the WHERE clause. This isn't a problem if my MySQL SP has only a couple of parameters as a simple sieve can handle which parameters are specified and which parameters are not specified, coding the SQL statement and it's specific execution accordingly.

However, this becomes a combinatorial problem if I have say 10 parameters that can be specified in any combination of content versus no content. Now the sieve code becomes quite lengthy posing potential performance, debugging, and maintenance problems.

Once again, the problem is not with the SP's code constructing the dynamic SQL, but with the actual execution call:

In MSSQL I can execute the dynamic SQL with one calling signature passing all potentially referenced variables (whether they get used or not).

In MySQL it appears that I have to have a separate call for each different combination of WHERE clause '?'s passing in the values in the sequence order of the '?'s (no more, or no less parameters than '?'s).

Am I missing something here?



Edited 1 time(s). Last edit at 05/23/2011 10:31AM by Peter Kierstead.