CODEWITHSUNDEEP
sql
user1970231
user1970231

Reputation: 17

SQL query about stored procedure parameter

I wanted know the reason, when we create a stored procedure in update, delete or insert like 

update TABLE_NAME set column_name = @variable_name

it is fine.

Why can't we pass parameter or variable to select like

select @column_variable from @table_variable

I know that as a work around you need to use dynamic SQL, but what is reason it won't work?

Upvotes: 1

Views: 104

Answers (1)

Andriy M
Andriy M

Reputation: 77687

If this is about SQL Server, then the reason why you cannot parametrise column names and table names with this statement,

select @column_variable from @table_variable

is because this can already be a valid statement and interpreted in a different way:

  • @name would be interpreted as a reference to a scalar variable whose value is to be returned as a dataset column;

  • @name would be interpreted as a table variable name, i.e. the name of a variable of a table type.

In each of these cases, the use of @name to denote a parameter holding the name of an actual column or table to select from would simply be very confusing.

On the other hand, one might think that a different syntax could have been devised for this (i.e. specifically for parametrisation of names) and yet it hasn't.

My opinion (and I have to admit that it's just my opinion) why there isn't such a syntax is that by building parametrisable names into SQL you would probably end up with less efficient query planner. If at the time of query compilation you don't know what and whence the query is trying to select, you can't really build a really efficient plan for it, can you.

Of course, building a query plan could have been delayed until the time when the name parameters have been evaluated, but the planner would have had to build a plan every time such a query is invoked, unlike now, when the query plan is stored once and then used many times.

Upvotes: 1

Related Questions