1. 创建存储过程
CREATE procedure LP_CreateTab
@Tab_NameInfo varchar(20),
@Tab_NameAchv varchar(20)
AS
BEGIN TRANSACTION
exec('Create Table '+@Tab_NameInfo+'
( Fd_No nvarchar(15),
Fd_XL nvarchar(10) ,
Fd_Master nvarchar(10),
Fd_StdNo nvarchar(3) primary key,
Fd_StdName nvarchar(20),
Fd_Age nvarchar(4) ,
Fd_Zoom nvarchar(15),
FOREIGN KEY (Fd_No) REFERENCES Tab_Class
)')
exec('Create Table '+@Tab_NameAchv+'
( Fd_No nvarchar(15),
Fd_StdNo nvarchar(3),
Fd_StdName nvarchar(20),
Fd_SubNo nvarchar(4) ,
Fd_SubName nvarchar(15),
Fd_Credit int,
Fd_Grd float,
Fd_KNo int,
Fd_KFlag nvarchar(4)
FOREIGN KEY (Fd_No) REFERENCES Tab_Class
)')
IF @@ERROR=0
BEGIN
COMMIT TRANSACTION
RETURN 0
END Else
BEGIN
ROLLBACK TRANSACTION
RETURN -1
END
GO
2. C#中调用
public void createTable(string tableNameInfo, string tableNameAchv)
{
try
{
sqlConnection1 = new SqlConnection(@"Data Source=(local);" + "Integrated Security=SSPI;" + "Initial Catalog=pubs");
sqlConnection1.Open();
sqlCommand1 = new SqlCommand("LP_CreateTab",sqlConnection1);
sqlCommand1.CommandType = CommandType.StoredProcedure;
sqlCommand1.Parameters.Add("@Tab_NameInfo",System.Data.SqlDbType.Char,20);
sqlCommand1.Parameters["@Tab_NameInfo"].Value = tableNameInfo;
sqlCommand1.Parameters.Add("@Tab_NameAchv",System.Data.SqlDbType.Char,20);
sqlCommand1.Parameters["@Tab_NameAchv"].Value = tableNameAchv;
sqlCommand1.ExecuteNonQuery();
sqlConnection1.Close();
}
catch(Exception error)
{
System.Diagnostics.Debug.Write(error.Message);
}
}