存储过程创建案例

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

ALTER procedure [dbo].[prc_create_table](
@banid varchar(50),
@name varchar(50),
@keyname varchar(50),
@banzhu bigint,
@ftstatus int,
@htstatus int,
@status int,
@remark text
)
as
declare @tiezhitabl varchar(100)--帖子表
declare @huitietabl varchar(100)--回帖表
declare @xgtiezitabl varchar(100)--帖子修改记录表
declare @jcbiaotabl varchar(100)--奖惩记录表
declare @sql1 varchar(500)
declare @sql2 varchar(500)
declare @sql3 varchar(500)
declare @sql4 varchar(500)

--帖子表cast(b as int)
set @tiezhitabl='gb_bbs_tiezhi_'+@banid
--回帖表
set @huitietabl='gb_bbs_huitie_'+@banid
--帖子修改记录表
set @xgtiezitabl='gb_bbs_xgtiezi_'+@banid
--奖惩记录表
set @jcbiaotabl='gb_bbs_jcbiao_'+@banid
--判断这张表是否存在
if exists(select name from sys.objects where name=''+@tiezhitabl+'')
begin
select 0 as msg--0表示表已存在，创建表失败
end
else
begin
set @sql1='create table '+@tiezhitabl+'(id bigint primary key identity,
title varchar(200) not null,
content text not null,
status int,
ifjj int,
ifdz int,
fttime datetime,
userid bigint FOREIGN KEY (userid) REFERENCES gb_bbs_user(id)
)'
if exists(select name from sys.objects where name=''+@huitietabl+'')
begin
select 0 as msg--0：表已存在，创建表失败
end
else
begin
set @sql2='create table '+@huitietabl+'(id bigint primary key identity,
tieziid bigint FOREIGN KEY (tieziid) REFERENCES '+@tiezhitabl+'(id),
content text,
status int,
fttime datetime,
userid bigint FOREIGN KEY (userid) REFERENCES gb_bbs_user(id)
)'
if exists(select name from sys.objects where name=''+@xgtiezitabl+'')
begin
select 0 as msg--0：表已存在，创建表失败
end
else
begin
set @sql3='create table '+@xgtiezitabl+'(id bigint primary key identity,
tieziid bigint FOREIGN KEY (tieziid) REFERENCES '+@tiezhitabl+'(id),
userid bigint FOREIGN KEY (userid) REFERENCES gb_bbs_user(id) ,
updatetime datetime,
content text
)'
if exists(select name from sys.objects where name=''+@jcbiaotabl+'')
begin
select 0 as msg --0：表示表已存在，创建表失败
end
else
begin
set @sql4='create table '+@jcbiaotabl+'(id bigint primary key identity,
tieziid bigint FOREIGN KEY (tieziid) REFERENCES '+@tiezhitabl+'(id),
time datetime,
content text
)'
begin tran --事务开始
--添加板块
insert into dbo.gb_bbs_bankuai ( name, keyname, banzhu, ftstatus, htstatus, status, remark) values (@name,@keyname,@banzhu,@ftstatus,@htstatus,@status,@remark)
exec(@sql1)
exec(@sql2)
exec(@sql3)
exec(@sql4)
if(@@error<>0)
begin
rollback tran
select 1 as msg--创建表遇到未知错误
end
-- else if(@@Trancount>0)
-- begin
-- rollback tran
-- end
else
begin
commit tran
select 2 as msg
end

end
end
end
end

execute prc_create_table '21','abc','abc',2,0,0,0,'abc'

 

=================================================

调用

protected void Page_Load(object sender, EventArgs e)
{
SqlConnection conn = null;
try
{
conn = new SqlConnection("server=.;database=n_xy28_data;uid=sa;pwd=123");
SqlCommand cmd = new SqlCommand();
SqlDataReader dr = null;
cmd.Connection = conn;
cmd.CommandText = "prc_create_table";
cmd.CommandType = CommandType.StoredProcedure;
//创建参数
IDataParameter[] parameters ={new SqlParameter("@banid",SqlDbType.VarChar,50),
new SqlParameter ("@name",SqlDbType .VarChar,50),
new SqlParameter ("@keyname",SqlDbType .VarChar,50),
new SqlParameter("@banzhu",SqlDbType .BigInt ),
new SqlParameter ("@ftstatus",SqlDbType .Int),
new SqlParameter ("@htstatus",SqlDbType .Int ),
new SqlParameter ("@status",SqlDbType .Int),
new SqlParameter ("@remark",SqlDbType.Text)
};
//设置参数
parameters[0].Value = "123456";
parameters[1].Value = "123456fda";
parameters[2].Value = "123abc";
parameters[3].Value = 2;
parameters[4].Value = 1;
parameters[5].Value = 1;
parameters[6].Value = 1;
parameters[7].Value = "此处是备注";
//添加参数
cmd.Parameters.Add(parameters[0]);
cmd.Parameters.Add(parameters[1]);
cmd.Parameters.Add(parameters[2]);
cmd.Parameters.Add(parameters[3]);
cmd.Parameters.Add(parameters[4]);
cmd.Parameters.Add(parameters[5]);
cmd.Parameters.Add(parameters[6]);
cmd.Parameters.Add(parameters[7]);
conn.Open();
Label1.Text = cmd.ExecuteNonQuery().ToString();

dr = cmd.ExecuteReader();

DataTable dt = new DataTable();
dt.Load(dr);
this.GridView1.DataSource = dt;
this.GridView1.DataBind();
}
catch (Exception ex)
{

Response.Write(ex.Message);
return;
}
finally {
conn.Close();
}

}