Thursday, March 14, 2013

Gotcha: BizTalk WCF-SQL Adapter & Table Variables

By now you probably know that the BizTalk WCF-SQL adapter doesn't like temporary tables in stored procedures when generating metadata (eg, refer to Thiago Almeida's post here http://connectedthoughts.wordpress.com/2011/01/18/wcf-sql-versus-the-old-sql-adapter/).

There are a couple of widely documented work-arounds, the two most common being the use of the FMTONLY setting in SQL Server (as described in Thiago's post) or replacing the use of temporary tables with table variables instead.

If you do decide to head down the path of replacing temporary tables in your stored procedures with table variables, something to be aware of is how variables are scoped within T-SQL.

Unlike other languages you might be familiar with such as C#, variables in T-SQL are scoped to the batch (or in the case of a stored procedure, to the entire stored procedure). So if say you have a loop in your stored procedure, and you declare a variable inside the loop, you may not get the results you expected. Because the scope of the variable is the entire stored procedure, it's not re-initialised each time around the loop (unless you do that explicitly), it's only re-declared - and in T-SQL, that means if it's already been declared the first time, there's nothing to do.

Where this ties in to the WCF-SQL adapter and the approach of replacing temporary tables in stored procedures with table variables is that a common scenario you may have is a T-SQL loop, where inside the loop's block you declare a temporary table, do some stuff, and at the end of the block you drop the temporary table (prior to the next iteration of the loop).

Unfortunately, when you replace this with a table variable instead, you end up with a similar statement to create the table variable at the start of the loop's block (replacing the CREATE TABLE... with a DECLARE @table TABLE... instead) - but there's no explicit way (at least that I know of) to do the equivalent of a DROP TABLE at the end of the block. If you combine this with T-SQL's scoping rules though, you can end up with some unexpected results.

For example:
 

Hmmm... If I was coming from a C# background, I might have expected the results to be somewhat different, more along the lines of a single record containing the iteration number each time around the loop. That's certainly what we would have got with the previous approach using create/drop of temporary tables.

The results are due to the @table table variable being re-declared but not re-initialised each time around the loop. So, how can we produce the behaviour of the previous temporary table approach without the ability to explicitly drop ("undeclare") the table variable? Well, the best I've come up with is to declare the table variable, and then immediately following it to include a DELETE statement to delete any records in the table variable. The first time around the loop of course this does nothing, but on subsequent iterations it deletes any records still present in the table variable from the previous iteration.

Using this approach we get the results expected:


HTH!

1 comment:

  1. The blog was absolutely fantastic! Lot of information. Thanks for sharing...

    Biztalk Online Training Hyderabad

    ReplyDelete