Multifacet VALUES clause

Till SQL 2008 VALUES clause was used only in conjunction with INSERT statements to insert values to a table. Starting from SQL 2008 onwards functionality of VALUES clause has been extended which enables it to be used in many scenarios effectively. This post aims at demonstrating few such scenarios.

1. Populating a table column with values from multiple variables

CREATE TABLE Test
(
Col1 int
)
DECLARE @Var1 int,@Var2 int,@var3 int
SELECT @Var1=2,@Var2 = 15,@Var3=118
INSERT Test
VALUES (@Var1),(@Var2),(@Var3)


Till 2008 this has to be done by means of INSERT and multiple SELECT statements separated by UNION ALL

2. Filtering a table based on multiple parameter values

CREATE PROCEDURE TestProc
(
@Param1 int,
@Param2 int
)
SELECT t.*
FROM Test t
INNER JOIN (VALUES (@Param1),(@param2)) p (val)
ON p.val = t.Col1
GO


This was earlier done by using IN operator or EXISTS operator or series of SELECT statements separated by UNION ALL


Also, this can even be extended to have filters based on multiple fields as well as shown by the example below


create table multiplefilterex
(
col1 int,
col2 varchar(10)
)


insert multiplefilterex
values (1,’test1′),
(2,’test234′),
(3,’werwrwq’),
(34,’csCEVEVEVE’)




CREATE PROCEDURE TestProc1
(
@Field1Value1 int,
@Field2Value1 varchar(10),
@Field1Value2 int,
@Field2Value2 varchar(10)
)
AS
SELECT t.*
FROM multiplefilterex t
INNER JOIN (VALUES ( @Field1Value1,@Field2Value1),(@Field1Value2,@Field2Value2)) p (val1,val2)
ON p.val1 = t.Col1
AND p.val2 = t.Col2
GO


EXEC TestProc1 3,’werwrwq’,1,’test1′


3. Creating an XML on the fly

declare @var varchar(10),@var1 varchar(10),@var2 varchar(10)
select @var = ‘test11’,@var1=’qwdwq d’,@var2=’wecwece’
select *
from (values(@var,@var1,@var2))t(var,var1,var2)
for xml path(‘varval’),root(‘varlist’)
see the output below

As you see from above all the values for XML are coming from variables. We can even have SELECT statements also in between to get part of XML value from it

EDIT: Adding one more scenario

4. UNPIVOT values from table

CREATE TABLE products 
(product_name varchar(10), 
color1 int, 
color2 int)


insert products
values (‘t-shirt’, 1, 3),
(‘short’, 2, 2)



–inserted values
select * from products


–unpivoted values
SELECT product_name,
color
 FROM products
 CROSS APPLY (VALUES (color1),(color2)
 ) AS t(color)

Now see the output

As you see values will get transformed into rows from columns which will give us UNPIVOT effect

These are few scenarios where you can effectively use the extended functionality of VALUES in SQL 2008 and later versions. This method is know as table constructor in 2008 and you can create up to 1000 rows using a single table constructor statement.

Published by dataplatformcentral