Sql-server – Error during sp_refreshsqlmodule after altering table type

sql serversql-server-2016t-sql

I am trying to add a column to a user-defined table type.
I have done some research and here is what I have now:

  -- First, rename existing table type to something else
EXEC sp_rename 'TT_MY_TABLE_TYPE', 'TT_MY_TABLE_TYPE_1'

-- Create the new table type
CREATE TYPE [dbo].[TT_MY_TABLE_TYPE] AS TABLE(
    [MY_FIELD] [varchar](20) NULL        
)
GO

-- Do a refresh of the SP/views so that the SP/views will refer to the new table type
-- Save the list of dependencies to a temporary table
SELECT  'sp_refreshsqlmodule ' + quotename('MySchemaName.' +object_name(referencing_id), '''') AS SQL_CMD
INTO    #TEMPSQL
FROM    sys.sql_expression_dependencies
WHERE   referenced_class_desc = 'TYPE' and referenced_entity_name =  'TT_MY_TABLE_TYPE';

DECLARE @sql NVARCHAR(1000)
-- Do a loop for the list of dependencies, use dynamic SQL to execute the SQL commands
DECLARE c_Cur CURSOR FOR
    SELECT  SQL_CMD
    FROM    #TEMPSQL

OPEN c_Cur
FETCH NEXT FROM c_Cur INTO @sql
WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC SP_EXECUTESQL @statement = @sql
    FETCH NEXT FROM c_Cur INTO @sql
END

CLOSE c_Cur
DEALLOCATE c_Cur

-- Drop the old table type
DROP TYPE TT_MY_TABLE_TYPE_1

DROP TABLE #TEMPSQL

Unfortunately, while refreshing stored/procedures (their dependencies) to reflect the new type I am getting the following error:

Procedure sp_refreshsqlmodule_internal, Line 85 [Batch Start Line 0]
Operand type clash: TT_MY_TABLE_TYPE is incompatible with TT_MY_TABLE_TYPE_1.

I expect that the error is something else since all I did was adding one column to the new type.

Could you help me identify, what is the root cause of this problem?

Best Answer

To anyone, that may come across this problem: The issue was nested dependencies. You need to run sp_refreshsqlmodule starting from the bottom of the dependency hierarchy. If you try to refresh the Stored Procedure, that uses this table type and passes it further, you will get this operand type clash error