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
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.