add checking to modify the length of a column ,drop and add the default value in one block

Question

I added a checking on a Varchar column that I want to modify its LENGTH , and its default value

table = EX_EMPLOYEE Column = TV_CODE length =10

My problem : I made 3 blocks to change the length then to drop the default value and add a new one. can I do that in one block ? is there alternative way for the below its a really long code can I make better checking with less condition and coes ?

BEGIN
DECLARE @OLD_LENGTH numeric(4)
DECLARE @isnull numeric(4)
DECLARE @null_value varchar(10)

SELECT @OLD_LENGTH= c.length,
       @isnull = c.isnullable
FROM syscolumns c
WHERE c.id = OBJECT_ID('EX_EMPLOYEE') AND c.name = 'TV_CODE'
if @isnull =1 
select @null_value='NULL'
else
select @null_value='NOT NULL'
IF @@ROWCOUNT =0
begin
 EXECUTE ('ALTER TABLE EX_EMPLOYEE ALTER COLUMN TV_CODE VARCHAR(11) '+@null_value)
end
 if  @OLD_LENGTH <11
 EXECUTE ('ALTER TABLE EX_EMPLOYEE ALTER COLUMN TV_CODE VARCHAR(11)'+ @null_value)
 END
GO
begin
DECLARE @df_value varchar(500)
select @df_value = d.name from sys.all_columns c join sys.tables t on t.object_id = c.object_id join sys.schemas s on s.schema_id = t.schema_id
join sys.default_constraints d on c.default_object_id = d.object_id
where t.name = 'EX_EMPLOYEE' and c.name = 'TV_CODE'
  if @df_value is not null
    begin
    EXECUTE ('ALTER TABLE EX_EMPLOYEE DROP CONSTRAINT '+ @df_value)
    end
end
go

IF EXISTS (SELECT 1 FROM sys.all_columns c WHERE c.object_id = OBJECT_ID('EX_EMPLOYEE') 
AND c.name = 'TV_CODE')
and NOT EXISTS(select * from sys.all_columns c join sys.tables t on t.object_id = c.object_id join sys.schemas s on s.schema_id = t.schema_id
join sys.default_constraints d on c.default_object_id = d.object_id
where t.name = 'EX_EMPLOYEE' and c.name = 'TV_CODE')
BEGIN
EXECUTE ('ALTER TABLE EX_EMPLOYEE ADD DEFAULT ''A'' for TV_CODE')
END
GO

score 1 · Accepted Answer · edited Oct 13 '17 at 08:08

There’s nothing wrong with the code you have. It can be done as one “block”, and there are ways to make it a bit more efficient, but multiple steps are required for what you are doing, so it will not be short. Here’s how I’d do it, based on the following assumptions:

Everything is in the DBO schema
If the column length is less than 11, do everything, otherwise do nothing

(Me, I’d want to be certain that columns are always NULL or NOT NULL, and don't vary from table instance to table instance. Enforcing this rule with this script would be wise.)

DECLARE
  @CurrentLength  smallint
 ,@IsNull         bit
 ,@Command        nvarchar(200)
 ,@Debug          bit = 1


--  Get current settings.  Note that variables are set to same dataype as is used in the tables
--  Also, always use the system tables in the "sys" schema
SELECT
   @CurrentLength = max_length
  ,@IsNull        = is_nullable
 from sys.columns
 where object_id = object_id('EX_EMPLOYEE')
  and name = 'TV_CODE'


IF @CurrentLength < 11
 BEGIN
    --  Table AND column exist, column is under 11 characters, so proceed with everything


    --  Here, reset column to varchar(11)
    SET @Command = 'ALTER TABLE EX_EMPLOYEE ALTER COLUMN TV_CODE VARCHAR(11) '
     + case @IsNull
         when 1 then 'NULL'
         else 'NOT NULL'
       end

    --  Whenever possible (which I think is always), use sp_executeSQL instead of EXECUTE()
    IF @Debug = 1
        --  Makes it easier to debug dynamic code
        PRINT @Command

    EXECUTE sp_executesql @Command


    --  Going to cheat here and adapt code from a prior answer https://stackoverflow.com/questions/1430456/how-to-drop-sql-default-constraint-without-knowing-its-name/1433384#1433384
    SET @Command = null

    SELECT @Command = 'ALTER TABLE EX_EMPLOYEE drop constraint ' + d.name
     from sys.tables t
      inner join sys.default_constraints d
       on d.parent_object_id = t.object_id
      inner join sys.columns c
       on c.object_id = t.object_id
        and c.column_id = d.parent_column_id
     where t.name = 'EX_EMPLOYEE'
      and c.name = 'TV_CODE'


    IF @Debug = 1
        --  Continues to make it easier to debug dynamic code
        PRINT @Command

    --  If there is no constraint, @Command will be null
    IF @Command is not null
        EXECUTE sp_executesql @Command


    --  Now create the default.  Give it a name, so we don't have to play "go fish" the next time around.
    --  Dynamic SQL is not required here.
    ALTER TABLE EX_EMPLOYEE
     add constraint DF_EX_EMPLOYEE__TV_CODE
      default 'A' for TV_CODE

 END
GO

I have some questions , for the default value you did add a checking ? if there is no default value exists for the column , an error will occur right ? — Moudiz, Oct 12 '17 at 06:37
Adding a default constraint has no effect on existing data. That would require an `update` statement. — Philip Kelley, Oct 12 '17 at 14:15
The second section builds a "drop constraint" statement, IF there is a constraint to drop. If there is no constraint to drop, it does nothing. Done this way, when we get to the "create constraint" section, there will be no constraint on that column to interfere with the creation. — Philip Kelley, Oct 12 '17 at 14:16
in this statement `SELECT @Command = 'ALTER TABLE EX_EMPLOYEE drop constraint ' + d.name...` if i execute it , it will not throws an error if d.name was null ? — Moudiz, Oct 12 '17 at 14:30
It is part of a SELECT statement. If a row is returned by the select, the referenced column (d.name) cannot be null, because of the logic of the joins and the underlying table structures. If no row is returned, the value of @Command will not be modified, which is why we have `SET @Command = null` immediately before it. Never forget, (anything) + NULL = NULL. Lastly, before the EXECUTE statement, we check if @Command is NULL, and if it is we do nothing... and I'm certain that executing a NULL statement would nothing, not even raise an error if it did get through. — Philip Kelley, Oct 12 '17 at 16:01

add checking to modify the length of a column ,drop and add the default value in one block

1 Answers1