Problem
Using dynamic SQL inside a procedure called by BizTalk as a strongly typed procedure through the WCF-SQL adapter can be very tricky.
Consider the following scenario: you have a procedure procA which retrieves some data in some variables, and then constructs dynamically a call to another stored procedure procB , based on the data previously retrieved.
For example, ProcA could look like something like:
CREATE PROCEDURE ProcA()
AS
DECLARE @extDB varchar(255),
@dynQuery varchar(MAX)
BEGIN
SELECT @extDB = ExternalDB
FROM dbo.SomeParamTable
SET @dynQuery = @extDB + ’.dbo.ProcB’
EXEC @extDB
END
If you try and execute this procedure with Management Studio – provided that you do have something in your table SomeParamTable and that ProcB exists in the specified database, of course – there should be no problem.
Nevertheless, when you have BizTalk execute the same procedure, or when you try and generate the typed schema, you might encounter the following error:
Could not find stored procedure ‘’ (And no proc name is specified)
The reason for this is that the WCF-SQL adapter first gets the metadata of the procedure it is about to execute before really executing it. This can be achieved by activating FMTONLY (using the statement: SET FMTONLY ON). I recommend reading some more specific information on FMTONLY (like this MSDN topic here), but basically when FMTONLY is ON, no statement is really executed.
For example, this means that the following statement:
SELECT @extDB = ExternalDB
FROM dbo.SomeParamTable
results in @extDB being null, whatever is in dbo.SomeParamTable., which implies that :
SET @dynQuery = @extDB + ’.dbo.ProcB’
results in @dynQuery being also null.
Possible solutions
There is no “generic” solution, only simple tricks that may fit your specific needs. I can think of three: one really easy, one messy, one more powerful but possibly dangerous. But whatever you do, always keep in mind that BizTalk needs to retrieve the metadata of your procedure, so you’d better be very careful.
Easy way
Simply put, the problem described earlier is only a problem of variables not being initialized. Therefore, you may provide a replacement value with isnull(), like that :
SET @dynQuery = isnull(@extDB, ‘MyDB’) + ’.dbo.ProcB’
Messy way
I don’t particularly like the messy way, but in some dramatic situations it may very well be your only solution: instead of using a Strongly-Typed Procedure (ie : action TypedProcedure/<Schema name>/<Proc name>), consider using an untyped Procedure (ie : action Procedure/[Schema name>/<Proc name>), in which case the adapter won’t have to get the metadata.
Hard way
You can also choose to deactivate FMTONLY inside of your procedure, at least for some parts of it. This can be done this way:
First, at the beginning of your procedure, copy/paste the following code.
-----------------------------------------------------------------
-- BizTalk : FMTONLY mgmt - Part 1
-----------------------------------------------------------------
DECLARE @fmtOnly bit
SET @fmtOnly = 0
IF (1 = 0)
BEGIN
SET @fmtOnly = 1
print 'FMTONLY is ON'
END
-----------------------------------------------------------------
-- BizTalk : FMTONLY mgmt - Part 1 end
-----------------------------------------------------------------
@fmtOnly is used to indicate whether the proc is being executed in FMTONLY mode or not.
If FMTONLY is ON, the result of the test 1 = 0 has no importance: the statements inside the IF section will be executed anyway. Then @fmtOnly is set to 1 (if you don’t believe me, execute the code above in Management Studio with FMTONLY ON 
)
If FMTONLY is OFF, 1 = 0 is always false so the statements inside of the IF section won’t be executed.
Then, before the section where you need to deactivate FMTONLY (in the previous example, it would be the section where we retrieve the value of @extDB from dbo.SomeParamTable), paste the following code:
-----------------------------------------------------------------
-- BizTalk : FMTONLY mgmt - Part 2
-----------------------------------------------------------------
IF (@fmtOnly = 1)
BEGIN
SET FMTONLY OFF
print 'FMTONLY was ON, now deactivated'
END
-----------------------------------------------------------------
-- BizTalk : FMTONLY mgmt - Part 2 end
-----------------------------------------------------------------
And after the end of the section, paste the following code:
-----------------------------------------------------------------
-- BizTalk : FMTONLY mgmt - Part 3
-----------------------------------------------------------------
IF (@fmtOnly = 1)
BEGIN
SET FMTONLY ON
print 'FMTONLY is reactivated'
END
-----------------------------------------------------------------
-- BizTalk : FMTONLY mgmt - Part 3 end
-----------------------------------------------------------------
That should do the trick…
Note : deactivating FMTONLY can be very dangerous because it means that the statements where it is deactivated will be really executed. It can lead to many unpleasant and unexpected phenomena, and sometimes it will be very hard to debug if you forget that the WCF-SQL adapter first gets the metadata THEN executes the procedure.