How to ALTER the Table Value Parameter

sql-server tsql table-valued-parameters
VInayK picture VInayK · Nov 14, 2009 · Viewed 22.8k times · Source

I am not getting option like 'ALTER TO' when am right clicking on TVP

Answer

Peter Radocchia picture Peter Radocchia · Nov 14, 2009

Can't do it. You must drop/recreate. If you have dependencies on the TVP, you must:

  1. create new TVP under new name
  2. alter dependencies to use (1)
  3. drop old TVP
  4. recreate (1) under original name
  5. alter dependencies to use (4)
  6. drop (1)

Related questions

TVP does not conform to table type

Below is the function that inserts my data. using (SqlCommand insSwipeDataCommand = connection.CreateCommand()) { insSwipeDataCommand.Transaction = transaction; insSwipeDataCommand.CommandType = CommandType.StoredProcedure; insSwipeDataCommand.CommandText = "dbo.insSwipeData_sp"; SqlParameter attendeeTableParam = insSwipeDataCommand.Parameters.Add("@AttendeeTable", SqlDbType.Structured); attendeeTableParam.Value = this.dataTable; attendeeTableParam.TypeName = "AttendeeTableType"; // …

Read More
Performance of User-Defined Table Types in SQL Server

We have been using User-Defined Table Types to pass a list of integers to our stored procedures. We then use these to join to other tables in our stored proc queries. For example: CREATE PROCEDURE [dbo].[sp_Name] ( @Ids [dbo].[…

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More