Administration · T-SQL coding

Substitute for PL/SQL %TYPE

Steve Henry

As a database developer using Oracle I always liked the option to declare variables in PL/SQL using a reference to a specific column, like this:

DECLARE v_MyID ORDER.ORDER_ID%TYPE

If the datatype changed on the column ORDER_ID above then my package or procedure code did not need to change; Oracle automatically changed the datatype in my code.  Nice!  However, SQL Server doesn’t have that option.

What you can do in SQL Server is to create a user-defined data type (UDT) and use that.  It does work, however, one of the main benefits of %TYPE in Oracle is that you can change the datatype of the column without touching your code.  Not so easy in SQL Server.  In fact, Microsoft makes it even harder for you, in that, you cannot modify the UDT (there is no ALTER TYPE statement).  Drop and re-create is the only option…AND you cannot drop a UDT if it is referenced by another object.

With that in mind most people don’t even bother trying to simulate the %TYPE in SQL Server.

Why use UDTs to define data?

  1. Better management of domains (i.e. better data quality)
  2. Programmer ease/stronger typed variables, etc.
  3. Easier to manage change when necessary

UDTs can be used in both your T-SQL coding (defining tables and variables, sorry but you can’t use UDTs in CAST or CONVERT) and in your .NET code wherever you use system data types (like defining input and output parameters).  The biggest negative to UDTs (and I will admit it is a BIG negative) is the fact that you can’t modify the UDT after it is in use without dropping all references to it first.

What follows is my attempt to make changing a UDT a bit more palatable…If you need to change your UDT I found that renaming the UDT then recreating it using the new datatype does work.  Not quite as easy as Oracle, but also not awful.  If my developers would like to use UDTs to help them code I believe I would recommend it since I have this technique to change the types when necessary.  In fact, I plan to start implementing some UDTs soon in our databases.  Here’s what I do in the example script:

  1. Create a UDT.
  2. Use the UDT in a CREATE TABLE and in a CREATE Procedure.
  3. Modify the UDT by renaming it and then re-creating it using the original name.
  4. Use an ALTER table to change the column definition.
  5. Re-compile the stored procedure without any code changes.
  6. The SP_HELP executions are in the script to show you the changes as they progress; they don’t do anything…

Here is my example script:

-- script to change a user-defined type using SQL Server 2014
-- S Henry 01/23/2019
-- 'GO' statements are important here; don't remove them.

-- Create the original type
CREATE TYPE dbo.MyColumnType FROM varchar(75) NOT NULL;
go

-- use the UDT in a table definition
create table dbo.MyTable (
     MyID int not null primary key clustered,
     MyColumn MyColumnType not null);
go

create procedure dbo.usp_MyProc 
     @MyInputParm MyColumnType 
as
     select @myInputParm;
go

--
-- rename the old UDT, then create another one with the original name and new attributes
--
exec sp_rename @objname = 'MyColumnType', @newname = 'MyOldColumnType', @objtype = userdatatype';

-- see the results! 
exec sp_help 'MyTable';
exec sp_help 'usp_MyProc';

CREATE TYPE dbo.MyColumnType FROM varchar(100) NOT NULL;
go

-- change the column to use the newly defined UDT
alter table dbo.MyTable 
alter column MyColumn MyColumnType not null;
go

-- just re-compile the procedure
ALTER procedure dbo.usp_MyProc 
     @MyInputParm MyColumnType 
as
     select @myInputParm;
go

-- see the results! 

exec sp_help 'MyTable';
exec sp_help 'usp_MyProc';

-- drop the test objects
drop type dbo.MyOldColumnType;
drop table dbo.MyTable;
drop Procedure dbo.usp_Myproc;
drop type dbo.MyColumnType;
go
--end of script

 

Leave a comment