database design – Very new to SQL syntax – can anyone give me the gist of what this code is doing?


I’m trying to improve my SQL skills and have just completed a couple basic courses on codecademy and SQLBolt. I’m looking into some of the SQL that’s used at a work place and unfortunately a lot of the terms such as ‘DECLARE’ ‘BEGIN’ and ‘SET’ are completely foreign to me still. It would be super useful to know what this code is doing so that I can work backwards and deconstruct it and try and work out the purpose of each line.

Many thanks in advance! Code below:

, @Length INT, @Precision INT= 0, @NotNull NVARCHAR(1)= 'N', @debug BIT=
0;
DECLARE @SQL NVARCHAR(MAX);


-- step 1
IF COLUMNPROPERTY(OBJECT_ID(@TblName), @ColName, 'ColumnId') IS NOT NULL
 BEGIN
 THROW 51000, 'Column already exists.', 1;
 RETURN 1;
END;
IF @DataType IN('nvarchar', 'varchar', 'decimal', 'numeric')
 AND (@Length IS NULL
 OR @Length = 0)
 BEGIN
 THROW 52000, 'The length field is required for a text or numeric type
column.', 1;
 RETURN 1;
END;
IF @DataType IN('decimal', 'numeric')
 AND @Precision IS NULL
 BEGIN
 THROW 52000, 'The precision field is required for a numeric type column.', 1;
 RETURN 1;
END;
IF @DataType IS NULL
 BEGIN
 THROW 53000, 'The data type field is required.', 1;
 RETURN 1;
END;


--step 2
SET @SQL = 'ALTER TABLE dbo.' + QUOTENAME(@TblName) + CHAR(10) + 'ADD '
+ QUOTENAME(@ColName) + ' ' + QUOTENAME(@DataType)
+ CASE
 WHEN @DataType IN('NVARCHAR', 'VARCHAR')
 THEN '(' + CAST(@Length AS NVARCHAR(4)) + ')'
 WHEN @DataType IN('DECIMAL', 'NUMERIC')
 THEN '(' + CAST(@Length AS NVARCHAR(4)) + ', ' + CAST(ISNULL(@Precision, 0) AS
NVARCHAR(4)) + ')'
 ELSE ''
 END + CASE
 WHEN @NotNull = 'Y'
 THEN ' NOT NULL'
ELSE ' NULL'
 END;
IF @debug = 1
 BEGIN
 PRINT @SQL;
END;
 ELSE
 EXEC (@SQL);```