求高手将SQL语句封装到存储过程中

Question

use Hipdc_FMH
insert into CompanyInfo select CompanyName,Corporation,Phone1,Phone2,PhoneFAX,Notes,GuaranteeSum,abbreviationCopName from newtest..CompanyInfo where newtest.CompanyInfo.CompanyID>(select max(CompanyID) from CompanyInfo); --公司表
insert into [User] select [Name],UserName,Password,Notes,Activity,CopId from newtest..[User] where newtest.[User].ID>(select max(id) from [User]);--用户表
insert into FunctionMold select FunctionPiceID,FunctionName,Notes from newtest..FunctionMold where newtest.FunctionMold.FunctionPiceID not in (select FunctionPiceID from FunctionMold);--功能模块表
delete from User_Function;--由于没有索引先删除用户与功能模块关系表
insert into User_Function select UserID,FunctionID from newtest..User_Function;--再将所有数据导入
insert into GoodsType select GoodsName,Notes,Activity,Plant_View from newtest..GoodsType where newtest.GoodsType.GoodsID>(select max(GoodsID) from GoodsType);--产品信息表
insert into DeliveryBill select PlanID,GoodsID,CompanyID,UserID,DeliveryTime,Weight,TruckNumber,TruckWeight,Notes,DeliveryActivity,DeliverySerialNumber,Place from newtest..DeliveryBill where newtest.DeliveryBill.DeliveryID>(select max(DeliveryID) from DeliveryBill);--销售表
insert into SaleBill select Goods,TruckNumber,SendCop,ReceiveCop,CarryCop,WeighTime,PrintTime,GrossWeight,TareWeight,NetWeight,Workers1_ID,Workers2_ID,Workers3_ID,Notes,DeliveryID,SallSerialNumber,ReceiveStyle,ReceiveShip,ShipId,Place from newtest..SaleBill where newtest.SaleBill.BillID >(select max(BillID) from SaleBill);--销售表
insert into ShipID select ShipNo,LoadTime,State from newtest..ShipID where newtest.ShipID.ShipId >(select max(ShipId) from ShipID);--船号表
要求两个数据库名能用参数传递,以上功能是在同一台服务器下进行的，用来进行数据库的数据同步。

Answer 1

create proc P
as
begin
insert into Hipdc_FMH..CompanyInfo select CompanyName,Corporation,Phone1,Phone2,PhoneFAX,Notes,GuaranteeSum,abbreviationCopName from newtest..CompanyInfo where newtest.CompanyInfo.CompanyID>(select max(CompanyID) from CompanyInfo); --公司表
insert into Hipdc_FMH.. [User] select [Name],UserName,Password,Notes,Activity,CopId from newtest..[User] where newtest.[User].ID>(select max(id) from [User]);--用户表
insert into  Hipdc_FMH..FunctionMold select FunctionPiceID,FunctionName,Notes from newtest..FunctionMold where newtest.FunctionMold.FunctionPiceID not in (select FunctionPiceID from FunctionMold);--功能模块表
delete from Hipdc_FMH..User_Function;--由于没有索引先删除用户与功能模块关系表
insert into Hipdc_FMH..User_Function select UserID,FunctionID from newtest..User_Function;--再将所有数据导入
insert into Hipdc_FMH..GoodsType select GoodsName,Notes,Activity,Plant_View from newtest..GoodsType where newtest.GoodsType.GoodsID>(select max(GoodsID) from GoodsType);--产品信息表
insert into Hipdc_FMH..DeliveryBill select PlanID,GoodsID,CompanyID,UserID,DeliveryTime,Weight,TruckNumber,TruckWeight,Notes,DeliveryActivity,DeliverySerialNumber,Place from newtest..DeliveryBill where newtest.DeliveryBill.DeliveryID>(select max(DeliveryID) from DeliveryBill);--销售表
insert into Hipdc_FMH..SaleBill select Goods,TruckNumber,SendCop,ReceiveCop,CarryCop,WeighTime,PrintTime,GrossWeight,TareWeight,NetWeight,Workers1_ID,Workers2_ID,Workers3_ID,Notes,DeliveryID,SallSerialNumber,ReceiveStyle,ReceiveShip,ShipId,Place from newtest..SaleBill where newtest.SaleBill.BillID >(select max(BillID) from SaleBill);--销售表
insert into Hipdc_FMH..ShipID select ShipNo,LoadTime,State from newtest..ShipID where newtest.ShipID.ShipId >(select max(ShipId) from ShipID);--船号表
end

Answer 2

每参数吗？create proc p_test
as
begin
insert into CompanyInfo 
select CompanyName,Corporation,Phone1,Phone2,PhoneFAX,Notes,GuaranteeSum,abbreviationCopName 
from newtest..CompanyInfo 
where newtest.CompanyInfo.CompanyID>(select max(CompanyID) from CompanyInfo); --公司表insert into [User] 
select [Name],UserName,Password,Notes,Activity,CopId 
from newtest..[User] 
where newtest.[User].ID>(select max(id) from [User]);--用户表insert into FunctionMold 
select FunctionPiceID,FunctionName,Notes
 from newtest..FunctionMold 
where newtest.FunctionMold.FunctionPiceID not in (select FunctionPiceID from FunctionMold);--功能模块表delete from User_Function;--由于没有索引先删除用户与功能模块关系表insert into User_Function 
select UserID,FunctionID 
from newtest..User_Function;--再将所有数据导入insert into GoodsType 
select GoodsName,Notes,Activity,Plant_View 
from newtest..GoodsType 
where newtest.GoodsType.GoodsID>(select max(GoodsID) from GoodsType);--产品信息表insert into DeliveryBill 
select PlanID,GoodsID,CompanyID,UserID,DeliveryTime,Weight,TruckNumber,TruckWeight,Notes,DeliveryActivity,DeliverySerialNumber,Place 
from newtest..DeliveryBill 
where newtest.DeliveryBill.DeliveryID>(select max(DeliveryID) from DeliveryBill);--销售表insert into SaleBill 
select Goods,TruckNumber,SendCop,ReceiveCop,CarryCop,WeighTime,PrintTime,GrossWeight,TareWeight,NetWeight,Workers1_ID,Workers2_ID,Workers3_ID,Notes,DeliveryID,SallSerialNumber,ReceiveStyle,ReceiveShip,ShipId,Place 
from newtest..SaleBill 
where newtest.SaleBill.BillID >(select max(BillID) from SaleBill);--销售表insert into ShipID 
select ShipNo,LoadTime,State 
from newtest..ShipID 
where newtest.ShipID.ShipId >(select max(ShipId) from ShipID);--船号表
end

Answer 3

create proc P
as
begin
insert into Hipdc_FMH..CompanyInfo select CompanyName,Corporation,Phone1,Phone2,PhoneFAX,Notes,GuaranteeSum,abbreviationCopName
from newtest..CompanyInfo where newtest.CompanyInfo.CompanyID>(select max(CompanyID) from  Hipdc_FMH..CompanyInfo); --公司表
insert into Hipdc_FMH.. [User] select [Name],UserName,Password,Notes,Activity,CopId from newtest..[User] where newtest.[User].ID>(select max(id) from  Hipdc_FMH..[User]);--用户表
insert into  Hipdc_FMH..FunctionMold select FunctionPiceID,FunctionName,Notes from newtest..FunctionMold where newtest.FunctionMold.FunctionPiceID not in (select FunctionPiceID from  Hipdc_FMH..FunctionMold);--功能模块表
delete from Hipdc_FMH..User_Function;--由于没有索引先删除用户与功能模块关系表
insert into Hipdc_FMH..User_Function select UserID,FunctionID from newtest..User_Function;--再将所有数据导入
insert into Hipdc_FMH..GoodsType select GoodsName,Notes,Activity,Plant_View from newtest..GoodsType where newtest.GoodsType.GoodsID>(select max(GoodsID) from  Hipdc_FMH..GoodsType);--产品信息表
insert into Hipdc_FMH..DeliveryBill select PlanID,GoodsID,CompanyID,UserID,DeliveryTime,Weight,TruckNumber,TruckWeight,Notes,DeliveryActivity,DeliverySerialNumber,Place from  newtest..DeliveryBill 
where newtest.DeliveryBill.DeliveryID>(select max(DeliveryID) from  Hipdc_FMH..DeliveryBill);--销售表
insert into Hipdc_FMH..SaleBill select Goods,TruckNumber,SendCop,ReceiveCop,CarryCop,WeighTime,PrintTime,GrossWeight,TareWeight,NetWeight,Workers1_ID,Workers2_ID,Workers3_ID,Notes,DeliveryID,SallSerialNumber,ReceiveStyle,ReceiveShip,ShipId,Place from newtest..SaleBill where newtest.SaleBill.BillID >(select max(BillID) from  Hipdc_FMH..SaleBill);--销售表
insert into Hipdc_FMH..ShipID select ShipNo,LoadTime,State from newtest..ShipID where newtest.ShipID.ShipId >(select max(ShipId) from  Hipdc_FMH..ShipID);--船号表
end

Answer 4

CREATE PROC up_test
@Dbname1 varchar(100),
@Dbname2 varchar(100)
AS EXEC('use '+@Dbname1+';
insert into CompanyInfo select CompanyName,Corporation,Phone1,Phone2,PhoneFAX,Notes,GuaranteeSum,abbreviationCopName from '+@Dbname2+'..CompanyInfo where '+@Dbname2+'.CompanyInfo.CompanyID>(select max(CompanyID) from CompanyInfo); '+'
insert into [User] select [Name],UserName,Password,Notes,Activity,CopId from '+@Dbname2+'..[User] where '+@Dbname2+'.[User].ID>(select max(id) from [User]);'+'
insert into FunctionMold select FunctionPiceID,FunctionName,Notes from '+@Dbname2+'..FunctionMold where '+@Dbname2+'.FunctionMold.FunctionPiceID not in (select FunctionPiceID from FunctionMold);--功能模块表
delete from User_Function;--由于没有索引先删除用户与功能模块关系表
insert into User_Function select UserID,FunctionID from '+@Dbname2+'..User_Function;--再将所有数据导入
insert into GoodsType select GoodsName,Notes,Activity,Plant_View from '+@Dbname2+'..GoodsType where '+@Dbname2+'.GoodsType.GoodsID>(select max(GoodsID) from GoodsType);--产品信息表
insert into DeliveryBill select PlanID,GoodsID,CompanyID,UserID,DeliveryTime,Weight,TruckNumber,TruckWeight,Notes,DeliveryActivity,DeliverySerialNumber,Place from '+@Dbname2+'..DeliveryBill where '+@Dbname2+'.DeliveryBill.DeliveryID>(select max(DeliveryID) from DeliveryBill);--销售表
insert into SaleBill select Goods,TruckNumber,SendCop,ReceiveCop,CarryCop,WeighTime,PrintTime,GrossWeight,TareWeight,NetWeight,Workers1_ID,Workers2_ID,Workers3_ID,Notes,DeliveryID,SallSerialNumber,ReceiveStyle,ReceiveShip,ShipId,Place from '+@Dbname2+'..SaleBill where '+@Dbname2+'.SaleBill.BillID >(select max(BillID) from SaleBill);--销售表
insert into ShipID select ShipNo,LoadTime,State from '+@Dbname2+'..ShipID where '+@Dbname2+'.ShipID.ShipId >(select max(ShipId) from ShipID);--船号表
')

Answer 5

动态语句也不能用 use，同一个服务器就把数据库名加上
if object_id('p_test') is not null drop proc p_test
go
create proc p_test
as
begin
insert into Hipdc_FMH..CompanyInfo 
select CompanyName,Corporation,Phone1,Phone2,PhoneFAX,Notes,GuaranteeSum,abbreviationCopName 
from newtest..CompanyInfo 
where newtest.CompanyInfo.CompanyID>(select max(CompanyID) from CompanyInfo); --公司表insert into Hipdc_FMH..[User] 
select [Name],UserName,Password,Notes,Activity,CopId 
from newtest..[User] 
where newtest.[User].ID>(select max(id) from [User]);--用户表insert into Hipdc_FMH..FunctionMold 
select FunctionPiceID,FunctionName,Notes
 from newtest..FunctionMold 
where newtest.FunctionMold.FunctionPiceID not in (select FunctionPiceID from FunctionMold);--功能模块表delete from Hipdc_FMH..User_Function;--由于没有索引先删除用户与功能模块关系表insert into Hipdc_FMH..User_Function 
select UserID,FunctionID 
from newtest..User_Function;--再将所有数据导入insert into Hipdc_FMH..GoodsType 
select GoodsName,Notes,Activity,Plant_View 
from newtest..GoodsType 
where newtest.GoodsType.GoodsID>(select max(GoodsID) from GoodsType);--产品信息表insert into Hipdc_FMH..DeliveryBill 
select PlanID,GoodsID,CompanyID,UserID,DeliveryTime,Weight,TruckNumber,TruckWeight,Notes,DeliveryActivity,DeliverySerialNumber,Place 
from newtest..DeliveryBill 
where newtest.DeliveryBill.DeliveryID>(select max(DeliveryID) from DeliveryBill);--销售表insert into Hipdc_FMH..SaleBill 
select Goods,TruckNumber,SendCop,ReceiveCop,CarryCop,WeighTime,PrintTime,GrossWeight,TareWeight,NetWeight,Workers1_ID,Workers2_ID,Workers3_ID,Notes,DeliveryID,SallSerialNumber,ReceiveStyle,ReceiveShip,ShipId,Place 
from newtest..SaleBill 
where newtest.SaleBill.BillID >(select max(BillID) from SaleBill);--销售表insert into Hipdc_FMH..ShipID 
select ShipNo,LoadTime,State 
from newtest..ShipID 
where newtest.ShipID.ShipId >(select max(ShipId) from ShipID);--船号表
end

Answer 6

7楼，动态语句中什么情况下不能用use?

Answer 7

动态语句可以使用USE,只是不能加GO.