There is a
method to constructing SQL statements in a way where the results are controlled
by a dynamically created statement. On other words, the SQL statement itself
can depend on some logic based on inputs. The straightforward way to do this is
to put together a character string that contains SQL, and then pass it to the
EXEC function to run.
There are
some dangers to doing an EXEC(). If you are relying from input from users in an
application, you subject the executed SQL to possible hacks via what’s known as
SQL Injection. To prevent this, you can instead call the sp_executesql()
function which will apply certain safeguards to the generated SQL string to
run. It also has better performance in some situations.
A place
where you cannot utilize dynamic SQL are user defined functions (UDFs). These
return a scalar value or a table. They cannot perform DDL operations. When they
return a table, there are two ways to code this: (1) an inline table value
function, or (2) a multi statement table value function.
Inline table value functions need to BEGIN and
END in the function. You just declare them to RETURN TABLE, and the body is
simply AS RETURN (SELECT …). Multi statement table value functions need to
specify RETURNS @my_table TABLE(
