September 13, 2018 by Kenneth Fisher
Recently I did a presentation on dynamic SQL. In the presentation I pointed out the similarity of using sp_executesql to creating a stored procedure to do the same task. After the session I was asked: If that’s the case, can I pass a TVP (table valued parameter) into sp_executesql?
Awesome question! Let’s give it a shot.
When using TVPs, the first thing you have to do is define the table type.
USE Test GO CREATE TYPE MyTVPType AS TABLE ( Col1 INT PRIMARY KEY); GO
The next we run the test. I’m using the TVP to check for values in a table. Similar to a dynamic IN clause.
USE Test GO -- Create a temp table with data to query from. CREATE TABLE #DynamicTVPTest (Col1 INT); INSERT INTO #DynamicTVPTest VALUES (1), (5), (67), (100), (301), (543), (997), (1111), (1245), (3356), (4295), (6546), (8342), (8567), (9000), (9265), (10045), (10321), (11456), (12545); GO -- Declare my TVP variable and add data. DECLARE @MyTVP MyTVPType; INSERT INTO @MyTVP VALUES (1), (5), (67), (100), (301), (543), (997), (3356), (9265), (11456), (12545); -- Declare my dynamic SQL variable and add the query. DECLARE @SQL nvarchar(4000); SET @SQL = N'SELECT * FROM #DynamicTVPTest WHERE Col1 IN (SELECT Col1 FROM @MyTVP)'; -- Run the whole thing. EXEC sp_executesql @SQL, N'@MyTVP MyTVPType READONLY', @MyTVP;
And it worked! In further proof of the sp_executesql is just like creating a SP theory I got the following error if I forgot the READONLY clause in the second parameter.
Msg 352, Level 15, State 1, Line 15
The table-valued parameter “@MyTVP” must be declared with the READONLY option.
Which is exactly what I would have expected if I was running this as an SP and forgot the READONLY.