Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

Using Table Valued Variables instead of Dynamic SQL

I had a situation today where I have a stored procedure that is structured as follows:

1 CREATE PROCEDURE _Entity_GetByKeywordTypeDateRange( 2 @keyword varchar(255) = null, 3 @typeA bit = 0, 4 @typeB bit = 0, 5 @typeC bit = 0, 6 @startDate datetime, 7 @endDate datetime) AS 8 BEGIN 9 10 SELECT 11 EntityId, 12 TypeId, 13 Field List 14 FROM 15 EntityTable 16 WHERE 17 Where Clause 18 END

Due to the framework we’re using, I don’t have a lot of control over the argument list here, unfortunately.  I was trying to find out the best way to handle the bit flags.  Types A, B, and C correspond to TypeId’s 1,2, and 3 respectively.

I had toyed with using some kind of progression of OR statements using CASE statements, but it was looking ugly and not working the way I wanted.

EX:

1 WHERE 2 (TypeId = CASE WHEN @typeA THEN 1 ELSE 1 END OR 3 TypeId = CASE WHEN @typeB THEN 2 ELSE 1 END OR 4 TypeId = CASE WHEN @typeC THEN 3 ELSE 1 END)

My next option was to do Dynamic SQL, and build an “IN” statement for the TypeId column, but I’m not a huge Dynamic SQL fan, and since there was no explicit column list, the framework I’m using might have choked on the definition and not properly created my data access objects.

EX:

1 declare @sql varchar(MAX) 2 declare @inlist varchar(10) 3 declare @valueAdded bit 4 5 set @valueAdded = 0 6 set @inlist = ( 7 if @typeA = 1 8 BEGIN 9 set @inlist = @inlist + 1 10 set @valueAdded = 1 11 END 12 13 if @typeB = 1 14 BEGIN 15 set @inlist = @inlist + 16 case 17 when @valueAdded = 1 18 then , 2 19 else 2 20 END 21 set @valueAdded = 1 22 END 23 24 if @typeC = 1 25 BEGIN 26 set @inlist = @inlist + 27 case when @valueAdded = 1 28 then , 3 29 else 3 30 END 31 END 32 33 set @inlist = @inlist + ) 34 35 set @sql = SELECT * from EntityTable WHERE TypeID IN + @inlist 36 37 EXECUTE @sql

This was kind of ugly and long.

Table Varaibles

SQL Server allows the definition of a variable of type TABLE.  This is essentially a temp table represented by a variable, and there are actually some advantages to using them over temporary tables, including less logging/locking overhead, etc.

This ended up being the simplest solution to the issue at hand.  See the code below for the sample solution.

1 declare @typeValues TABLE (TypeId int) 2 3 if @typeA INSERT INTO @TypeValues (TypeId) VALUES (1) 4 if @typeB INSERT INTO @TypeValues (TypeId) VALUES (2) 5 if @typeC INSERT INTO @TypeValues (TypeId) VALUES (3) 6 7 SELECT 8 Field List 9 FROM 10 EntityTable as e INNER JOIN @typeValues as t 11 ON e.TypeId = t.TypeId

Simple, Clean, and no temporary tables to manage.

Table variables can be invaluable in solving some annoying T-SQL issues in place of Dynamic SQL or temporary tables.

For a more advanced example of T-SQL Table Variables in action, check out this MSDN Article: Dr. Tom’s Workshop: How Table Variables Can Speed Up Your Queries

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

\\\