Guru's Guide to SQL Server™ Stored Procedures, XML, and HTML, The

A Better Example

Now that you've seen how easy it is to put together a simple extended procedure, let's try one that's a little more complex. The code that follows in Listing 20-10 implements an extended procedure named xp_exec. As I mentioned in the chapter on UDFs, you aren't allowed to call EXEC() from within a UDF. Only extended procedures can be called, and only ones that begin with xp (sp_executesql isn't allowed even though it's an extended procedure because it doesn't begin with xp). Xp_exec works around this. You pass it a query you'd like to run, and it returns a result set, if there is one. Although you can't insert this result into a table variable (INSERT…EXEC isn't supported with table variables), you can manipulate tables and data, ...