T-Sql存储过程基于参数动态添加一列到表中 [英] T-Sql Stored Procedure to Dynamically Add a Column to a Table Based on Parameters

问题描述

可能的重复:
ALTER TABLE my_table ADD @column INT

谁能告诉我是否有可能有一个有效的存储过程:

Can anyone tell me if it is possible to have a stored procedure that is effectively this:

USE [dbname]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [Schema].[CreateColumn]
    @Table varchar(255),
    @ColumnName varchar(255)
AS
BEGIN
    BEGIN TRANSACTION
        SET QUOTED_IDENTIFIER ON
        SET ARITHABORT ON
        SET NUMERIC_ROUNDABORT OFF
        SET CONCAT_NULL_YIELDS_NULL ON
        SET ANSI_NULLS ON
        SET ANSI_PADDING ON
        SET ANSI_WARNINGS ON
    COMMIT

    BEGIN TRANSACTION
        ALTER TABLE [Schema].@Table ADD @ColumnName varchar(255) NULL
        ALTER TABLE [Schema].@Table SET (LOCK_ESCALATION = TABLE)
    COMMIT
END
GO

基本上，我可以传入一个表名和列名，它会在该表上使用适当的事务创建它.

Basically, I could pass in a table name and column name and it would create it on that table with proper transactions.

推荐答案

对不起，这是不可能的.请参阅此网站.

I'm sorry, it's not possible. See this website.

很抱歉，但事实并非如此.

I'm sorry, but that is not true.

然而，它很笨拙.您几乎可以简单地执行内嵌 SQL(如果是表单客户端代码)或每次简单地编写 SQL 语句，因为您每次都需要定义表名、列名、列数据类型和默认值您执行(除非您对其中的一些参数进行硬编码，即列数据类型始终为 VarChar(255).

It is, however, clumsy. You are almost as well off simply executing in-line SQL (if form client code) or simply composing an SQL statement each time, as you need to define the Table name, the Column name, The Column DataType, and the default value each time you execute (unless you hard-code some of those parameters in i.e. the column data type will ALWAYS be VarChar(255).

尝试各种变化，看看您是否找到了您要找的东西.但是，可能有更好的方法来实现您的目标:

Play with variations on this, and see if you find what you are looking for. However, there are probably better ways to acheive what you are trying to do:

DECLARE @sql NChar(4000);

SET @Sql = N'ALTER TABLE [Schema].' 
    + @TableName + ' ADD ' 
    + @ColumnName + ' ' 
    + @ColumnType + ' ' 
    + @InitialValue

EXECUTE sp_executesql @sql
GO

这篇关于T-Sql存储过程基于参数动态添加一列到表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！