Executing a table type parameter stored procedure or function within a select clause

Question

Just a quick question. I have a stored procedure that takes two parameters, a varchar (table name) and a tabletype (range of primary key ID values), and uses them to return all FK connections to and from the table for the provided ID's.

The usage is tricky though, since you actually need to declare the tabletype, select the ID's into it, and then execute the procedure like so:

DECLARE @IDVALS SearchObjRef_Type

INSERT INTO @IDVALS 
SELECT TOP 10 RAND_ID FROM RANDTABLE 

EXEC usp_SearchObjRef @TABLENAME = 'RANDTABLE', @IDRANGE = @IDVALS

This works just fine, but I'd like to find a more comfortable, fast and easy way to use the procedure in standard selects. What would be the best way, assuming it's even possible, to do all that in one command?

For a logical example that obviously doesn't work, what I'd like to do is a form of the following:

EXEC usp_SearchObjRef @TABLENAME = 'RANDTABLE', 
    @IDRANGE = (SELECT TOP 10 RAND_ID FROM RANDTABLE)

Answer 1

The way you're doing it is about the fastest, easiest and most comfortable way you're going to find to pass a TVP to a stored procedure, sorry. You can't pass expressions to stored procedure parameters, even simple things like:

EXEC sp_who2 N'act'+'ive';

Fails with:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '+'.

In order to use an expression, it needs to be:

DECLARE @expression SYSNAME = N'act'+'ive';
EXEC sp_who2 @expression;

Answer 2

If the output is a single result set that always has the same columns (e.g. TableName, RowCount) you could write a table valued CLR function and take the query string as parameter. There is no real clean way to do this in plain T-SQL.