Table-Valued Parameters need an alias!

September 3rd, 2012 by Rhys

I’m always the one to say RTFM but this one stumped me for a while. I had problems using a Table-Valued Parameter in a Stored Procedure today.

The explanation is indeed in the documentation but it’s perhaps not as clear as it should be. Hopefully this post will be picked up better in Google for those having similar issues.

Here’s a quick outline of the problem…

Firstly, create a TVP type, just a simple list of integers…

?View Code TSQL
-- Create a simple TVP
CREATE TYPE dbo.ListOfIntegers AS TABLE
(
	id INTEGER NOT NULL PRIMARY KEY CLUSTERED
);
GO

Now let’s create a simple proc using this type.

?View Code TSQL
-- Simple proc using this tvp
CREATE PROCEDURE dbo.testtvp
(
	@table AS dbo.ListOfIntegers READONLY
)
AS
BEGIN
 
	SELECT *
	FROM @table;
 
END
GO

Does it work?

?View Code TSQL
-- Let's use it!
DECLARE @table AS dbo.ListOfIntegers;
INSERT INTO @table (id) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
EXEC dbo.testtvp @table;
GO

OK, that works, let’s try something a little more complicated…

?View Code TSQL
-- OK, that works, how about this....
CREATE PROCEDURE dbo.testtvp2
(
	@table AS dbo.ListOfIntegers READONLY,
	@table2 AS dbo.ListOfIntegers READONLY
)
AS
BEGIN
 
	SELECT *
	FROM @table
	INNER JOIN @table2
		ON @table.id = @table2.id
END
GO

This will not compile…

-- Get the following errors

Msg 137, Level 16, State 1, Procedure testtvp2, Line 12

Must declare the scalar variable @table.

Msg 137, Level 16, State 1, Procedure testtvp2, Line 12

Must declare the scalar variable @table2.

The documentation does indeed say; "When you use a table-valued parameter with a JOIN in a FROM clause, you must also alias it" but it’s perhaps not as prominent as it could be. So let’s alias the Table-Valued Parameters…

?View Code TSQL
CREATE PROCEDURE dbo.testtvp3
(
	@table AS dbo.ListOfIntegers READONLY,
	@table2 AS dbo.ListOfIntegers READONLY
)
AS
BEGIN
 
	SELECT *
	FROM @table AS t1
	INNER JOIN @table2 AS t2
		ON t1.id = t2.id
END
GO

This will compile and function as expected…

?View Code TSQL
DECLARE @table AS dbo.ListOfIntegers;
INSERT INTO @table (id) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
DECLARE @table2 AS dbo.ListOfIntegers;
INSERT INTO @table2 (id) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
EXEC dbo.testtvp3 @table, @table2;
GO

Happy TSQL’ing!

Posted in SQL Server, T-SQL | No comments

Previous post:
Next post:

Leave a comment