存储过程 新建表 表名为参数

一.语法示例

USE [Exam]
GO
/****** Object:  StoredProcedure [dbo].[addExamTable]    Script Date: 2014/12/26 14:10:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[addExamTable]
	@newTableName VARCHAR(50),
	@result NVARCHAR(255) OUTPUT
 AS
 BEGIN
	DECLARE @sql VARCHAR(3000)
	SET @sql = 'CREATE TABLE'+@newTableName +'(
		[id] [uniqueidentifier] PRIMARY KEY   NOT NULL,
		[学号] [char](11) NULL,
		[姓名] [varchar](15) NULL,
		[班次] [varchar](4) NULL,
		[考号] [varchar](10) NULL,
		[顺序号] [varchar](4) NULL,
		[考室] [varchar](20) NULL,
		[考试地点] [varchar](20) NULL,
		[所在校区] [varchar](10) NULL,
		[语文] [float] NULL,
		[语卷] [float] NULL,
		[语卡] [float] NULL,
		[语作] [float] NULL,
		[数学] [float] NULL,
		[数卷] [float] NULL,
		[数卡] [float] NULL,
		[英语] [float] NULL,
		[外卷] [float] NULL,
		[外卡] [float] NULL,
		[物理] [float] NULL,
		[物卷] [float] NULL,
		[物卡] [float] NULL,
		[化学] [float] NULL,
		[化卷] [float] NULL,
		[化卡] [float] NULL,
		[生物] [float] NULL,
		[生卷] [float] NULL,
		[生卡] [float] NULL,
		[政治] [float] NULL,
		[政卷] [float] NULL,
		[政卡] [float] NULL,
		[历史] [float] NULL,
		[历卷] [float] NULL,
		[历卡] [float] NULL,
		[地理] [float] NULL,
		[地卷] [float] NULL,
		[地卡] [float] NULL
	) ON [PRIMARY]'
	 EXEC (@sql)
	SET @result='succeed'	
 END

二.在程序中用下面的代码实现给上述存储过程中的@tablename传递值

   SqlParameter[] para={ new SqlParameter("@newqishu",SqlDbType.Int)};
   para[0].Value =newQishu;
   DBHelper.ExecuteNonQuery("PR_CREATETABLE",para,CommandType.StoredProcedure);

//其中,DBHelper.ExecuteNonQuery()方法的代码如下:
public static int ExecuteNonQuery(string cmdText, SqlParameter[] parms, CommandType cmdtype)
  {
   int retVal;
   using (SqlConnection conn = new SqlConnection(connString))
   {
    SqlCommand cmd = new SqlCommand(cmdText, conn);
    cmd.CommandType = cmdtype;

    if ( parms != null)
    {
     //添加参数
     foreach (SqlParameter parm in parms)
     {
      cmd.Parameters.Add(parm);
     }
    }

    conn.Open();       
                
    retVal = cmd.ExecuteNonQuery();        
    conn.Close();
   }

   return retVal;
  }