Pass Function value to Stored Procedure in MS SQL

Question

I have a Function called dbo.GetFoo(). I also have a unit-testing Stored Procedure called AssertEqual (which takes @TargetValue sql_variant, @ExpectedValue sql_variant, and @Message varchar)

I want to call GetFoo() and check to see if it's returning the right value 'X'. My T-SQL statement is:

exec AssertEqual dbo.GetObjectType(), 'S', 'Check If S'

I get this message:

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

It appears to be choking on the "dbo." part (I can pass it a literal string and it works fine).

Is there any way around this, other than declaring a variable for the targeted value?

Alternately: is there a better way to do unit testing for SQL?

Answer 1

That's a limitation of T-SQL: it doesn't like user-defined function calls passed as parameters to a stored procedure. You have to declare a variable.