July 24, 2015 at 11:35 am
Hey all,
Is there a good way to add columns to a table type?
I built several procs which make use of table-valued-parameters, and they work pretty nicely, until I need them to accept additional columns. Then I have to drop all the procs that use them, alter the types, and rebuild all the procedures, which is a huge pain in the rear.
Is there any good way (built in, or custom) to alter the def of a table type that's used as a parameter to multiple stored procedures?
July 24, 2015 at 11:42 am
Xedni (7/24/2015)
Hey all,Is there a good way to add columns to a table type?
I built several procs which make use of table-valued-parameters, and they work pretty nicely, until I need them to accept additional columns. Then I have to drop all the procs that use them, alter the types, and rebuild all the procedures, which is a huge pain in the rear.
Is there any good way (built in, or custom) to alter the def of a table type that's used as a parameter to multiple stored procedures?
If you are using a table type that frequently, then you probably need a permanent table instead, and a slight re-design of your procs to deal with it. That way, additional columns may only mean a refresh of the procs instead of a drop and re-create, unless you need to specifically do something with those additional columns in the proc.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 24, 2015 at 11:44 am
So the way I'm using them is in procedures which are part of an API which allows people to put in configuration rows which eventually get put into pres tables. Many of these config tables can and do contain multiple rows, so I'm left with either using XML, comma-delimited-strings, or table valued parameters.
A persisted table wouldn't get me anything, since I'd still need a way to collect the multi-row data from the caller.
July 24, 2015 at 12:01 pm
Xedni (7/24/2015)
So the way I'm using them is in procedures which are part of an API which allows people to put in configuration rows which eventually get put into pres tables. Many of these config tables can and do contain multiple rows, so I'm left with either using XML, comma-delimited-strings, or table valued parameters.A persisted table wouldn't get me anything, since I'd still need a way to collect the multi-row data from the caller.
Okay, then maybe you need Jeff Moden's string splitter routine, so that you can use delimited strings, and you can choose your own single-character delimiter. Look in the Articles on this site, for ones written by Jeff, and the topic or subject will start with "Tally Oh". It provides a TVF whose performance rivals that of CLR for taking a delimited string and returning a table of values. Even if you don't use it, it's still a great read.
However, I still don't see any easy way to avoid having to recode for additional columns without including the column names as data items, and then handling that in the proc dynamically. Whether or not that's practical may be a whole different story. If the API can't take the user data and populate a permanent table, that just seems truly odd, and I guess I just don't see why it has to be multiple rows.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 24, 2015 at 12:07 pm
Thanks for the doc on the string splitter. I'm quite familiar with it (we use those string splitters regularly). You may have already answered my question when you said there's not a good way to do it.
I think maybe I'm not making the use case clear though. Lets take a trivial example (this is NOT my use case BTW but should illustrate it). Say someone wants to input a person, along with their immediate relatives. They might have two brothers, 16 nephews, 4 grand parents etc.
The input procedure might look something like this:
create type dbo.tRelatives as table
(
RelativeName varchar(30),
RelativeType varchar(30)
)
go
create table dbo.Relatives
(
MainPersonID int,
RelativeName varchar(30),
RelativeType varchar(30)
)
go
create proc insertRelatives
@mainPersonId int,
@relatives tRelatives readonly
as
insert into dbo.Relatives
(
mainPersonID,
RelativeName,
RelativeType
)
select
@mainPersonID,
RelativeName,
RelativeType
from @Relatives
go
declare @tRelatives dbo.tRelatives
insert into @tRelatives values('Bob', 'uncle'), ('tom', 'uncle'), ('nick', 'brother')
exec insertRelatives 1, @tRelatives
Then, I now want to keep the birthday of the relatives as well. This is the crux of my issue. I have additional columns I want to be able to work off into the presentation table, dbo.Relatives. FWIW, I'm just trying to avoid having to shift to using XML as the input since there are already a lot of TVP's in use.
July 24, 2015 at 12:16 pm
There is a best way to change UDTTs; unfortunately it requires a good standard operating procedure the same as for changing Tables. Database objects are intricately connected in a hierarchical fashion. Changing an object nearly always requires unconnecting that object, changing it, and reconnecting or recreating dependent objects.
For UDTTs, I wrote a stored procedure to call that specifically drops dependent procedures and functions for a UDTT name passed in. Actually I have a hierarchy of procedures that get called for making any change. I've created custom versions of the microsoft scripting actions so I can control everything how I want it done. It takes years to get all this in place. Afterwards, any change only takes minutes. I also depend heavily on TFS (source safe).
July 24, 2015 at 12:19 pm
July 24, 2015 at 12:38 pm
I just rename the existing user table type to some other type name -- which you can then immediately drop -- then (re)create the original type name with the new column(s) I wanted. You will have to explicitly run "EXEC sp_refreshsqlmodule" on any proc(s)/module(s) affected; you can of course generate those commands ahead of time. To me that's much better than DROP, CREATE of the module(s), esp. since no permissions are lost this way.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 24, 2015 at 12:55 pm
This is for the dependent procs:
IF EXISTS
(
SELECT *
FROM sys.procedures
WHERE name = 'DropUdttDependentProcs'
AND type = N'P'
AND schema_id = SCHEMA_ID('dbo')
)
DROP PROCEDURE dbo.DropUdttDependentProcs
GO
CREATE PROCEDURE DropUdttDependentProcs
@UdttName varchar(128)
AS
DECLARE
@ProcedureName varchar(128),
@drop varchar(MAX),
@sql varchar(MAX)
;
SET @drop =
'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''dbo.{ProcedureName}'') AND type in (N''P'', N''PC''))
DROP PROCEDURE dbo.{ProcedureName}
;
';
SET @ProcedureName = '';
GOTO NextProcedureName;
WHILE @ProcedureName IS NOT NULL
BEGIN
SET @sql = REPLACE(@drop,'{ProcedureName}',@ProcedureName);
EXEC (@sql);
NextProcedureName:
SET @ProcedureName =
(
SELECT MIN(p.name)
FROM sys.procedures p
join sys.parameters par ON p.object_id = par.object_id
JOIN sys.types t ON par.user_type_id = t.user_type_id
WHERE t.name = @UdttName
AND t.is_table_type = 1
);
END
RETURN 0;
GO
and this is for the dependent functions:
IF EXISTS
(
SELECT *
FROM sys.procedures
WHERE name = 'DropUdttDependentFunctions'
AND type = N'P'
AND schema_id = SCHEMA_ID('dbo')
)
DROP PROCEDURE dbo.DropUdttDependentFunctions
GO
CREATE PROCEDURE DropUdttDependentFunctions
@UdttName varchar(128)
AS
DECLARE
@FunctionName varchar(128),
@drop varchar(MAX),
@sql varchar(MAX),
@udtt_object_id int
;
SET @udtt_object_id = (select user_type_id from sys.types where name = @UdttName and is_table_type = 1);
SET @drop =
'IF EXISTS
(
SELECT *
FROM sys.objects
WHERE name = ''{FunctionName}''
AND type in (N''FN'', N''IF'', N''TF'')
AND objects.schema_id = SCHEMA_ID(''dbo'')
)
DROP FUNCTION dbo.{FunctionName}
;
';
SET @FunctionName = '';
GOTO NextFunctionName;
WHILE @FunctionName IS NOT NULL
BEGIN
SET @sql = REPLACE(@drop,'{FunctionName}',@FunctionName);
EXEC (@sql);
NextFunctionName:
SET @FunctionName =
(
SELECT top (1) OBJECT_NAME(referencing_id)
FROM sys.sql_expression_dependencies sed
WHERE sed.referenced_class = 6 --type
AND sed.referenced_id = @udtt_object_id
);
END
RETURN 0;
GO
July 24, 2015 at 12:59 pm
But don't you have to save the permissions on those objects before you drop them?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 24, 2015 at 1:04 pm
ScottPletcher (7/24/2015)
I just rename the existing user table type to some other type name -- which you can then immediately drop -- then (re)create the original type name with the new column(s) I wanted. You will have to explicitly run "EXEC sp_refreshsqlmodule" on any proc(s)/module(s) affected; you can of course generate those commands ahead of time. To me that's much better than DROP, CREATE of the module(s), esp. since no permissions are lost this way.
That sounds like exactly what I need. I'll give that a shot. Thank you all for your input on this!
July 28, 2015 at 10:50 am
Were you able to use the rename approach?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply