I would like to use EXECUTE for calling a scalar valued function as dynamic SQL and for assigning the return value to a variable. This is working fine as long as the scalar valued function does not have parameters. E.g.:
However when trying the same with a scalar valued function that requires parameters I am running into problems:
='dbo.CheckLimit ''SELECT 1'', 1, 1'
SQL Server thinks dbo.CheckLimit should be a stored procedure, but a stored procedure with that name does not exist:
Msg 2812, Level 16, State 62, Line 6
Could not find stored procedure 'dbo.CheckLimit 'SELECT 1', 1, 1'.
Can anybody tell if there is a way to call a scalar valued function in that way? I intend to use this inside a table valued function, so calling a stored procedure instead is no option.
My customer is currently using SQL Server 2005.
Anticipative thanks and kind regards
Softfount IT Solutions