sql server programming: Hi All,

What is the difference between executing
1) SELECT statement and
2) Declaring a string variable which contains the SELECT
query and executing it through EXEC() call.

Does the User need any extra access rights for the second
case than required for the first case.

Cheers,

Most often, you do this inside a stored procedure. Downsides:

It is no longer enough to have EXECpermissions on the procedure for the
user. The user need permissions to do whatever is EXECuted.
Performance may degrade because less efficient caching of query plans.
Harder to read and maintain SQL code.
Bigger risk that you get SQL-injection (security risk).

Read about dynamic SQL at:
http://www.algonet.se/~sommar/

--
Tibor Karaszi


[quoted text, click to view]