change DataType from Type as Table

Question

How can i update the data Type
Types ->UserDefined Data Types.
I want to change the MakeID from int to varchar(100)
ModelID from int to varchar(100)

CREATE TYPE [dbo].[TestDataType] AS TABLE(
     [MakeID] [int] NOT NULL,
     [Make] [varchar](200) NOT NULL,
     [ModelID] [int] NOT NULL,
     [Model] [varchar](200) NOT NULL,
     [Year] [int] NOT NULL
 )
 GO

Below is my stored proc. Is there anyway I can update my sproc by avoid using @tv_TeraData as [TeraDataType]
Just use a Regular Table instead of UserDefinedTable

ALTER PROCEDURE [dbo].[usp_TeraData_Insert]
(
@tv_TeraData as [TeraDataType] READONLY
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
INSERT INTO dbo.TeraData
(
[VhlMakeID],[VhlMake],[VhlModelID],[VhlModel],[VhlSubModelID],[VhlSubModel],[VhlTypeID],[VhlType],[VhlYear], [CreatedDate]
)
SELECT *, CURRENT_TIMESTAMP
from (select distinct [VhlMakeID],[VhlMake],[VhlModelID],[VhlModel],
[VhlubModelID],[VhlSubModel],[VhlTypeID],[VhlType],[VhlYear]
FROM @ttv_TeraData) AS Tera
WHERE NOT EXISTS (SELECT 1
FROM dbo.TeraData Where TeraData.VhlMakeID = Tera.VhlMakeID
and TeraData.VhlSubModel = Tera.VhlSubModel
and TeraData.VhlTypeID = Tera.VhlTypeID
and TeraData.VhlType = Tera.VhlType
and TeraData.VhlYear = Tera.VhlYear
and TeraData.VhlMake = Tera.VhlMake
and TeraData.VhlModelID = Tera.VhlModelID
and TeraData.VhlModel = Tera.VhlModel
and TeraData.VhlSubModelID = Tera.VhlSubModelID
);

END

Answer 1

If now you want to get rid of @tv_TeraData and you usually insert single rows (i.e. @tv_TeraData was filled with a single row before calling the stored procedure), then replace @tv_TeraData with a series of parameters:

ALTER PROCEDURE [dbo].[usp_TeraData_Insert]
(
     @MakeID [int] NOT NULL,
     @Make [varchar](200) NOT NULL,
     @ModelID [int] NOT NULL,
     @Model [varchar](200) NOT NULL,
     @Year [int] NOT NULL
)
as 
begin
 insert into dbo.TeraData ([VhlMakeID],[VhlMake],[VhlModelID],[VhlModel], [VhlYear], [CreatedDate] )
 select *, CURRENT_TIMESTAMP from (
    select @MakeID, @Make, @ModelID, @Model, @Year
    except
    select [VhlMakeID], [VhlMake], [VhlModelID], [VhlModel], [VhlYear] from dbo.TeraData ) d
end

Answer 2

You cannot ALTER a User-Defined Table Type (UDTT). You will need to drop and recreate it.

Unfortunately, that means that you will also need to first drop any stored procedures and/or functions where you are using this UDTT as an input parameter (i.e. a Table-Valued Parameter / TVP). Then, after recreating the TVPP, you can recreate the objects that were using the UDTT as an input parameter.

Is there anyway I can update my sproc by avoid using @tv_TeraData as [TeraDataType]

The only other decent way to pass in a table of data (multiple rows of multiple columns) would be to convert the data to XML at the source, change the parameter datatype to XML, and then replace the UDTT in your INSERT query with the @XmlParam.nodes. But I don't know why you would do that when you already have it set up in probably the best way.

P.S. you should prefix the TVP param in the proc with dbo.

Answer 3

This question was asked and answered in your previous post on the same subject.

Is there a problem with the answer?
ALTER TABLE [dbo].[TestDataType]
ALTER COLUMN [MakeID] VARCHAR(100) NOT NULL;
GO
ALTER TABLE [dbo].[TestDataType]
ALTER COLUMN [ModelID] VARCHAR(100) NOT NULL;

Please see:
https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver15

Answer 4

If you want to avoid the TYPE because it is difficult to edit it, then maybe consider the next method.

Instead of editing, create a new one, for example:

CREATE TYPE [dbo].[TestDataType2] AS TABLE (
      [MakeID] [varchar](200) NOT NULL,
      [Make] [varchar](200) NOT NULL,
      [ModelID] [varchar](200) NOT NULL,
      [Model] [varchar](200) NOT NULL,
      [Year] [int] NOT NULL
) 

Then adjust the stored procedures to use the new type:

ALTER PROCEDURE [dbo].[usp_TeraData_Insert]
(
   @tv_TeraData as [TeraDataType2] READONLY
)
AS 
. . .

Also adjust the procedure that executes usp_TeraData_Insert.

Then you can delete the old type:

DROP TYPE [dbo].[TestDataType]

Actually, you can repeat such steps to revert back from TeraDataType2 to TeraDataType.

Answer 5

Below is my stored proc. Is there anyway I can update my sproc by avoid using @tv_TeraData as [TeraDataType]

Yes, for instance you can move to XML instead as Solomon suggested. However, I don't see the point with this. Drop the procedure, drop the table type, create the new version of the table type, recreate the procedure. Yes, it's not fantastic, but everything else would be to through out both baby and bathtub.