Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:




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:

share|improve this question
How about converting it to an inline TVF? If the original sproc returns two results sets, you'll need to split it up. – Jon Seigel Apr 24 '13 at 13:30
...actually, that wouldn't work, as you must be doing dynamic SQL in the sproc. – Jon Seigel Apr 24 '13 at 13:39
The proc returns just one table, basically showing the count of references from each table / column to referred table / column for the range of ID's. But it's worth looking up, if only because I haven't done those before. Thanks! – Kahn Apr 24 '13 at 13:40
up vote 1 down vote accepted

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;
share|improve this answer
Alright, well it's good to know. Thanks! – Kahn Apr 25 '13 at 6:13

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.

share|improve this answer
And again, good to know. Thanks! :) – Kahn Apr 25 '13 at 6:14

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.