各位大虾，SQL 存储过程中怎么创建数据表，并且插入数据

Question

    SQL 查询分析器  在存储过程中怎么创建数据表，并且插入数据。 我的这个怎么报错      create procedure TimeDataPro 
       as 
if exists(select * from sysobjects where name ='tb_tmps')  
   drop table tb_tmps create table tb_tmps  
(
tbId_tp int Identity(1,1),
val1 nvarchar(100),
val2 nvarchar(200),
val3 nvarchar(300)
)
go insert into tb_tmps(val1,val2,val3) values ('aa','bb','cc')
       go
   报错信息：
   消息 208，级别 16，状态 1，第 2 行
   对象名  'tb_tmps' 无效。

Answer 1

create table #tb_tmps  
( 
tbId_tp int Identity(1,1), 
val1 nvarchar(100), 
val2 nvarchar(200), 
val3 nvarchar(300) 
) 

Answer 2

create procedure TimeDataPro 
      as 
if exists(select * from sysobjects where name ='tb_tmps')
drop table tb_tmps 
go 
create table tb_tmps  
( 
tbId_tp int Identity(1,1), 
val1 nvarchar(100), 
val2 nvarchar(200), 
val3 nvarchar(300) 
) 
go insert into tb_tmps(val1,val2,val3) values ('aa','bb','cc') 
      go 
select * from tb_tmps
/*tbId_tp     val1                                                                                                 val2                                                                                                                                                                                                     val3
----------- ---------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           aa                                                                                                   bb                                                                                                                                                                                                       cc
2           aa                                                                                                   bb                                                                                                                                                                                                       cc(2 行受影响)*/

Answer 3

 
      create procedure TimeDataPro
      as
if exists(select * from sysobjects where name ='tb_tmps') 
  drop table tb_tmpscreate table tb_tmps 
(
tbId_tp int Identity(1,1),
val1 nvarchar(100),
val2 nvarchar(200),
val3 nvarchar(300)
)
 --去掉go
insert into tb_tmps(val1,val2,val3) values ('aa','bb','cc')
      go  TimeDataProselect * from tb_tmps
/*tbId_tp     val1                                                                                                 val2                                                                                                                                                                                                     val3
----------- ---------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           aa                                                                                                   bb                                                                                                                                                                                                       cc(1 行受影响)*/

Answer 4

最好写成这样
create procedure TimeDataPro 
      as 
if exists(select * from sysobjects where name ='tb_tmps')
drop table tb_tmps 
go 
if object_id('procName')is not null 
drop procedure TimeDataPro
go
create table tb_tmps  
( 
tbId_tp int Identity(1,1), 
val1 nvarchar(100), 
val2 nvarchar(200), 
val3 nvarchar(300) 
) 
go insert into tb_tmps(val1,val2,val3) values ('aa','bb','cc') 
      go 
select * from tb_tmps
/*tbId_tp     val1                                                                                                 val2                                                                                                                                                                                                     val3
----------- ---------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           aa                                                                                                   bb                                                                                                                                                                                                       cc
2           aa                                                                                                   bb                                                                                                                                                                                                       cc(2 行受影响)*/