Take the 2-minute tour ×
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. It's 100% free, no registration required.

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)
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
add comment

2 Answers 2

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
add comment

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
add comment

Your Answer

 
discard

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.