alter table with case

[mflancour]

I'm creating a table dynamically and need to be able to use a case for the column length. if it is an int or date or whatever there is no column length, but if it's some other I need to include it in my statement.
the table I'm using to create this table contains:
column_name column_datatype column_length column_comment
firstname VARCHAR 100 Contains first name
birthday DATETIME NULL Contains birthday

DECLARE cur_feedcolumns CURSOR FOR
SELECT *  FROM #temp_feedcolumns
OPEN cur_feedcolumns
FETCH NEXT FROM cur_feedcolumns INTO @vc_column_id, @vc_column_name, @vc_column_datatype, @vc_column_length, 
	@vc_column_comment, @vc_updatable, @vc_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
	SELECT @sql = 'EXEC (''ALTER TABLE [' + @v_listdatabase + '].dbo.' + @p_listname +  
		' ADD '' + @vc_column_name + @vc_column_datatype + 
		CASE WHEN @vc_column_length IS NULL THEN '''' ELSE ''('' + @vc_column_length + '')'' END + '' NULL'')'
	PRINT @sql 
	EXEC sp_executesql @sql, N'@vc_column_name VARCHAR(200), @vc_column_datatype VARCHAR(20), @vc_column_length INT', 
		@vc_column_name, @vc_column_datatype, @vc_column_length

	FETCH NEXT FROM cur_feedcolumns INTO @vc_column_id, @vc_column_name, @vc_column_datatype, @vc_column_length, 
		@vc_column_comment, @vc_updatable, @vc_name
END
CLOSE cur_feedcolumns
DEALLOCATE cur_feedcolumns

 

[rac2]

Why use two levels of execute and variables?

Why not build a string with the appropriate ALTER TABLE statement variables and execute that string?

And use IF statements instead of CASE. A CASE function is used in an expression in a SQL statement, not for flow control in a procedure.

SET @sql = 'ALTER TABLE [' + @v_listdatabase + '].dbo.'
SET @sql = @sql + @p_listname + ' ADD ' 
SET @sql = @sql + @vc_column_name + ' ' 
SET @sql = @sql + @vc_column_datatype 

IF @vc_column_length IS NOT NULL
BEGIN
  SET @sql = @sql + '(' + @vc_column_length + ')' 
END 
SET @sql = @sql + ' NULL'

PRINT @sql

EXECUTE( @sql )