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

一.语法示例

USE [Exam]GO/****** Object:  StoredProcedure [dbo].[addExamTable]    Script Date: 2014/12/26 14:10:30 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[addExamTable]@newTableName VARCHAR(50),@result NVARCHAR(255) OUTPUT AS BEGINDECLARE @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;  }