Dynamically executing stored procedures with output parameters

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

	SET @dynamicSql = 'exec ' + @proc + ' ''' + @Actor1 + ''', '''+@Actor + ''''
	EXECUTE sp_ExecuteSQL @dynamicSql

view raw gistfile1.txt hosted with ❤ by GitHub

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:

	DECLARE @proc varchar(255), @dynamicSql varchar(255), @ParamDefinition varchar(255), @Actor1 nvarchar(255),@Actor2 nvarchar(255)
	SET @proc = 'OnScreenChemistry'
	--*cursor declare/fetch stuff*
	--set @Actor1, @Actor2 values here from your cursor
	--create your query with placeholders for the parameters to your stored proc
	SET @dynamicSql = 'exec ' + @proc + '@Actor1Param,@Actor2Param,@ResultParam output'
	--create definitions for your parameter placeholders
	Set @ParamDefinition = '@Actor1Param nvarchar(255) ,@Actor2Param nvarchar(255), @ResultParam float(53) output'
	-- call your stored proc using your dynamic query and pass in values to your parameters.
	execute sp_executesql
	@dynamicsql
	,@ParamDefinition
	,@Actor1Param = @Actor1
	,@Actor2Param = @Actor2
	,@ResultParam = @Result output

view raw gistfile1.sql hosted with ❤ by GitHub

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!