This short post will detail two sample scenarios where a table variable provides a better solution when working within certain constraints.

Recently when working on a query for an SSRS report it became apparent that the complexity of the query could not be managed in a single query.  The complexity could be reduced by breaking the query into two parts: one which populated a temporary table and then a second simpler query against that table.  This meant writing a stored procedure as the query type for the reports dataset. 

However in this particular case, due to constraints at the client site, it was preferable not to add a stored procedure to the database.  A text based query which could be embedded in the reports dataset was preferable.  Table variables offered a solution to this problem.  The scope of table variables means that they are created implicitly when the DECLARE statement is called and dropped explicitly at the end of the batch of statements.  This allowed them to be used in a text based query.

Shortly after a second case arose where another client wanted to run a “like” query against field on a table which had a data type of XML.  Due to the structure of the XML the easiest way to execute the required “like” in the where clause was to first convert the data to a nvarchar(max).  The solution which emerged was to select the data into a temporary table, casting the XML data as a nvarchar(max) .  Due to restrictions in this live environment it was not possible to create a temporary table and drop it again.  However given the implicit creation and dropping of a table variable it was possible to use one of these in this case. 

Here’s a simplified example:

DECLARE @tempXML TABLE(id uniqueidentifier,, version int, xmlcontent nvarchar(max))

insert into @tempXML
select id, version ,cast(xmlcontent as nvarchar(max))
FROM OriginalTableContainingXMLField
select distinct id from @tempXML
where xmlcontent like '%<Node>Interesting data</Node>%'

Of course you can chose to take a number of other approaches -as with everything it’s about choosing the right tool for the task at hand.

Should you find yourself working on future projects where a temporary table cannot be used to solve your query - don’t panic- try and use the SQL Server table variable to resolve the issue.

At Dataworks we enable the perfect hybrid of configurable off the shelf toolsets and custom software development to deliver innovative solutions to match your specific business process requirements. This ensures we are the best at what we do.

If you would like to discuss how we can use our experience and expertise to deliver real benefits to your business please contact us today on 051 878555051 878555 or email info@dataworks.ie

Read our next blog post on Design By Contract and Unit Testing: Why You Shouldn’t Overlook Design By Contract as a Complement To Unit Testing

Image courtesy of Stuart Miles / FreeDigitalPhotos.net