Stored Procedure: Variable Variable Names

Question

I want to make it easier to insert new defaults into existing columns from my setup. For a better overview and less errors, I want to make an oneliner from a three-line layer with redundant names. My first attempt at storing dprocedure for this:

CREATE PROCEDURE InsertDefault
    -- Add the parameters for the stored procedure here
    @TableName VARCHAR(200),
    @FieldName VARCHAR(200),
    @DefaultValue VARCHAR(200)
AS
BEGIN
    IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(@TableName) AND name = @FieldName)
        ALTER TABLE @TableName ADD @FieldName [int] CONSTRAINT CONCAT('DF_', @TableName, '_', @FieldName) DEFAULT @DefaultValue NOT NULL
    GO
END
GO

But it ALTER TABLE @TableNamedoes not seem to work, and not concatenation for the name of the constraint.

What am I missing here?

And then is it possible to make a @DefaultValuemixed type?

Answer 1

You should use dynamic sql (do not forget to avoid single quotes in the query string, if present):

declare @cmd nvarchar(4000)
select @cmd = 'ALTER TABLE ' + @TableName + ' ADD ' + @FieldName 
       + ' [int] CONSTRAINT ' + CONCAT('DF_', @TableName, '_', @FieldName) 
       + ' DEFAULT ' + @DefaultValue + ' NOT NULL'
exec sp_executesql @cmd

Answer 2

....

BEGIN
    IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(@TableName) AND name = @FieldName)
        exec('ALTER TABLE ' + @TableName + 'ADD ' + @FieldName + ' [int] CONSTRAINT CONCAT(''DF_'', ' + @TableName + ','' _'', ' + @FieldName + ') DEFAULT ' + @DefaultValue + ' NOT NULL')
    GO
END