Using a User defined table type with a temporary stored procedure

Question

I am having trouble using a user defined table type as a parameter type to a temporary stored procedure.

I am doing all of this within the database that houses our temp tables, temp stored procedures,... temp anything.

The code looks like this:

CREATE TYPE test_type AS TABLE (user int, user_value int) 
GO

CREATE PROCEDURE #test_pro 
    @input test_type READONLY
AS
    SELECT TOP 10 * 
    FROM @input 
GO

I get the error:

Parameter or variable @input has an invalid data type.

Is there a way for me to use the data type test_type as a type in my temporary stored procedure? I unfortunately only have access to temporary stored procedures and not the permanent version.

Side question: would using a function work any better?

Answer 1

You need to create the UDT in tempdb.

The following works fine

USE tempdb

GO

CREATE TYPE test_type AS TABLE ([user] int, user_value int)

GO

--Switch database
USE msdb

GO

CREATE PROCEDURE #test_pro @input TEST_TYPE READONLY
AS
    SELECT TOP 10 *
    FROM   @input

GO

EXEC #test_pro

GO

USE tempdb

DROP TYPE test_type

DROP PROC #test_pro 

If you don't have CREATE TYPE permissions in tempdb you can potentially use a #temp table instead.

e.g. the following works

CREATE PROCEDURE #test_pro 
AS
    SELECT TOP 10 * 
    FROM #input 
GO

GO

CREATE TABLE #input([user] int, user_value int) 

GO

EXEC #test_pro