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