新手发帖，关于语法和结构,该如何处理

新手发帖，关于语法和结构
因为对最后执行的Update语句不是很有信心，所以要创建表，并向表中插入数据。想把这些动作放到存储过程中，一旦Update没明白可以通过执行存储过程让表以及表中的数据恢复到Update之前的状态。
但实际写的时候消息框里一串串红字，纠结，帮忙看一下

SQL code

if object_id('ChuShiHua') is not null Drop procedure ChuShiHuaCreate procedure ChuShiHuaasbegin    if Object_ID('SK_Silk') is not null Drop table SK_Silk    Create Table SK_Silk    (        silk_own int,        cardID varchar(50),        cStatus varchar(50)    )    go    Insert into SK_Silk    Select 11,'aaa','b' union    Select 7,'a1a','1' union    Select 14,'3aa','5' union    Select 55,'a5','6'    go    if Object_ID('_OnOffline') is not null Drop table _OnOffline    Create Table _OnOffline    (        silk_own int,        cardID varchar(50),        cStatus varchar(50)    )    go    Insert into _OnOffline    Select 14,'aaa','b' union    Select 7,'at','1' union    Select 11,'3ya','5' union    Select 7,'a77','6'    go    if Object_ID('_User') is not null Drop table _User    Create Table _User    (        silk_own int,        cardID varchar(50),        cStatus varchar(50)    )    go    Insert into _User    Select 14,'a0a','b' union    Select 9,'at','1' union    Select 11,'3ya','5' union    Select 12,'a77','6'    go    sp_rename '[SK_Silk].cardID','charId','COLUMN'    go    sp_rename '[_OnOffline].cardID','charId','COLUMN'    go    sp_rename '[_User].cardID','charId','COLUMN'    go    Select * from SK_Silk    Select * from _OnOffline    Select * from _User    goendgo

执行后消息框提示：
消息 111，级别 15，状态 1，过程 ChuShiHua，第 5 行
'CREATE/ALTER PROCEDURE' 必须是查询批次中的第一个语句。
消息 102，级别 15，状态 1，过程 ChuShiHua，第 11 行
')' 附近有语法错误。

(4 行受影响)

(4 行受影响)

(4 行受影响)
消息 15248，级别 11，状态 1，过程 sp_rename，第 213 行
参数 @objname 不明确或所声明的 @objtype (COLUMN)有误。
注意: 更改对象名的任一部分都可能会破坏脚本和存储过程。
注意: 更改对象名的任一部分都可能会破坏脚本和存储过程。

(36 行受影响)

(4 行受影响)

(4 行受影响)
消息 102，级别 15，状态 1，第 1 行
'end' 附近有语法错误。


另外虽然有错，存储过程执行后

SQL code

sp_helptext ChuShiHuago

结果显示为：

Text
---------------------------------------------------------------------------------------------------------------
Create procedure ChuShiHua
as
if Object_ID('SK_Silk') is not null Drop table SK_Silk
Create Table SK_Silk
(
silk_own int,
cardID varchar(50),
cStatus varchar(50)
)



我的后半截跑哪里去了，不是用了Begin...end吗。

相当纠结+无比惆怅...

[解决办法]
存储过程中不能go

SQL code

if object_id('ChuShiHua') is not null Drop procedure ChuShiHuaGOCreate procedure ChuShiHuaasbegin    if Object_ID('SK_Silk') is not null Drop table SK_Silk    Create Table SK_Silk    (        silk_own int,        cardID varchar(50),        cStatus varchar(50)    )    Insert into SK_Silk    Select 11,'aaa','b' union    Select 7,'a1a','1' union    Select 14,'3aa','5' union    Select 55,'a5','6'    if Object_ID('_OnOffline') is not null Drop table _OnOffline    Create Table _OnOffline    (        silk_own int,        cardID varchar(50),        cStatus varchar(50)    )    Insert into _OnOffline    Select 14,'aaa','b' union    Select 7,'at','1' union    Select 11,'3ya','5' union    Select 7,'a77','6'    if Object_ID('_User') is not null Drop table _User    Create Table _User    (        silk_own int,        cardID varchar(50),        cStatus varchar(50)    )    Insert into _User    Select 14,'a0a','b' union    Select 9,'at','1' union    Select 11,'3ya','5' union    Select 12,'a77','6'    sp_rename '[SK_Silk].cardID','charId','COLUMN'    sp_rename '[_OnOffline].cardID','charId','COLUMN'    sp_rename '[_User].cardID','charId','COLUMN'    Select * from SK_Silk    Select * from _OnOffline    Select * from _Userendgo 
 
[解决办法]
SQL code
if object_id('ChuShiHua') is not null Drop procedure ChuShiHuagoCreate procedure ChuShiHuaasbegin    if Object_ID('SK_Silk') is not null Drop table SK_Silk    Create Table SK_Silk    (        silk_own int,        cardID varchar(50),        cStatus varchar(50)    )    Insert into SK_Silk    Select 11,'aaa','b' union    Select 7,'a1a','1' union    Select 14,'3aa','5' union    Select 55,'a5','6'    if Object_ID('_OnOffline') is not null Drop table _OnOffline    Create Table _OnOffline    (        silk_own int,        cardID varchar(50),        cStatus varchar(50)    )    Insert into _OnOffline    Select 14,'aaa','b' union    Select 7,'at','1' union    Select 11,'3ya','5' union    Select 7,'a77','6'    if Object_ID('_User') is not null Drop table _User    Create Table _User    (        silk_own int,        cardID varchar(50),        cStatus varchar(50)    )    Insert into _User    Select 14,'a0a','b' union    Select 9,'at','1' union    Select 11,'3ya','5' union    Select 12,'a77','6'    exec sp_rename '[SK_Silk].cardID','charId','COLUMN'    exec    sp_rename '[_OnOffline].cardID','charId','COLUMN'    exec   sp_rename '[_User].cardID','charId','COLUMN'    Select * from SK_Silk    Select * from _OnOffline    Select * from _Userend 

 

查看更多 下一篇