Dynamic SQL and User defined Functions..

Question

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

Here's the allowed list:

Assignment statements.
Control-of-Flow statements.
DECLARE statements defining data variables and cursors that are local to the function.
SELECT statements containing select lists with expressions that assign values to variables that are local to the function.
Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.
INSERT, UPDATE, and DELETE statements modifying table variables local to the function.
EXECUTE statements calling an extended stored procedures.

In order to execute Dynamic SQL queries, you're either doing:

EXEC(<Dynamic SQL String>)

or

EXEC sp_executesql <Dynamic SQL String>

Which would violate the above list. You can use EXEC, but for extended stored procedures (xp_*).

K. Brian Kelley

K. Brian Kelley
@kbriankelley

William Markham SSC Enthusiast Points: 196 More actions · Answer 2

You could make a stored procedure that creates a table. Then you can pass more things to it.

Bill