4

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)
  • 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
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;
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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