A very interesting case crops up when you are trying to execute dynamically created SQL using sp_executesql when you have to pass in output parameters.
Let's assume that you have a stored procedure called 'OnScreenChemistry' which is an ingenious algorithm that you created to calculate the chemistry between movie stars.
It takes in 2 input parameters - Actor1, Actor2 of type varchar(255) and returns an output parameter of MatchScore which is a float that reflects on screen chemistry through a normalized range of 0 - 1.
Assuming you're trying to compare on-screen chemistry between all actors in your fictitious Actor table.
You can use a cross join to compare the actors and put each Actor1,Actor2 parameter inputs into 2 variables via a cursor.
Having these 2 variables you could create create SQL dynamically using
Yes, I know it's ugly, but it's a simple way to create dynamic SQL, when all you need to do is to provide input parameters.
However, the catch is that you you can't add an output parameter in the same manner since you need to provide a placeholder for the output variable, instead of a value like you can for input parameters.
So here's how you can call the stored proc dynamically using output parameters:
At this point, your @Result variable should contain the value from the stored proc. You could perform an insert of this variable along with your @Actor1 and @Actor2 variables into a result table for instance. It's upto you!
