Why was this T-SQL Syntax never implemented?

Question

Why did they never let us do this sort of thing:

Create Proc RunParameterisedSelect

@tableName   varchar(100),
@columnName  varchar(100),
@value       varchar(100)

as

select * from @tableName
where @columnName = @value

You can use @value as a parameter, obviously, and you can achieve the whole thing with dynamic SQL, but creating it is invariably a pain.

So why didn't they make it part of the language in some way, rather than forcing you to EXEC(@sql)?

Answer 1

Because procedures precompile on first execution into query plans, and this would not be possible like that. I agree it is paifull, it is only so, though,f or people which are stored procedure addicts - do not use sp's for queriey, build dynamic query strings and you have no problems.

Answer 2

Quite simply because such a thing is impossible. This is a bit like asking, "Why was this JavaScript syntax never implemented":

var operator  = "<";
var statement = "if"
var op1       = 4

statement (op1 operator 5) op1++;  // whatever

It's never been implemented because this is un-implementable and, frankly, it does not make any sense. JavaScript has eval() for dynamic code:

code = statement+" (op1 "+operator+" 5) op1++;";
eval( code );

And SQL Server has EXECUTE for dynamic SQL:

/* example only, it is not recommendable to actually *do* this */
Create Proc RunParameterisedSelect
  @tableName   varchar(100),
  @columnName  varchar(100),
  @value       varchar(100)
as
begin 
  declare @code varchar(8000)

  set @code = 'select * from ['+@tableName+'] where ['+@columnName+'] = '+@value

  exec (@code)
end

The essence is the same - if it is not a fixed, immutable code structure (and a table or column name is code in SQL, not a variable), then you must make a string out out it first and parse that. The interpreter/compiler must build a new syntax tree (which itself will be fixed again, of course).