alter Procedure [dbo].[GetProtypeList]
@DataBase_InsertUser bigint,
@DataBase_ID bigint
AS
DECLARE @DataBase_Address varchar(50)
DECLARE @DataBase_LoginName varchar(50)
DECLARE @DataBase_DBS varchar(50)
DECLARE @DataBase_Table varchar(50)
DECLARE @DataBase_PassWord varchar(50)
BEGIN
select @DataBase_Address=[DataBase_Address],@DataBase_LoginName=[DataBase_LoginName],
@DataBase_DBS=[DataBase_DBS],@DataBase_Table=[DataBase_Table],
@DataBase_PassWord=[DataBase_PassWord]
FROM DataBaseInfo WHERE [DataBase_DelFlag]=0
AND [DataBase_ID]=@DataBase_ID declare @s varchar(8000)
set @s='INSERT INTO ProtypeInfo SELECT
[PT_ID],@DataBase_ID,[PT_Name],'',[PT_Level],[PT_ParentID],[PT_Unit],[PT_IsShow],[PT_IsGuide],[PT_IsIndex],
[PT_FilterAttribute],[PT_PriceInterval],[PT_StylePath],[PT_KeyWord],[PT_Describe],[PT_Properties],[PT_IsEndType],
[PT_DelFlag],getdate(),@DataBase_InsertUser,[PT_OrderNum],[PT_IsTrue],[PT_S1],[PT_S2],[PT_S3],[PT_S4],[PT_S5]
FROM
DB.PDB.DBO.ProtypeInfo
OPENROWSET
(''sqloledc'','+@DataBase_Address+';'+@DataBase_LoginName+';'+@DataBase_PassWord+','+@DataBase_DBS+'.DBO.'+@DataBase_Table+''')'
exec (@s)
END
已经完成命令
但是在调用此存储过程时,却老提示必须声明标量变量 @DataBase_ID?这是为什么呢?请帮忙
调用方法如下public Boolean UA_DataBaseInfo_GetProtypeInfo(Int64 DataBase_ID, Int64 DataBase_InsertUser)
{
DbCommand dbcmd = db.GetStoredProcCommand("GetProtypeList");
db.AddInParameter(dbcmd, "@DataBase_ID", DbType.Int64, DataBase_ID);
db.AddInParameter(dbcmd, "@DataBase_InsertUser", DbType.Int64, DataBase_InsertUser);
Int64 result = 0;
try
{
result = db.ExecuteNonQuery(dbcmd);
}
catch (Exception ex)
{
throw ex;
}
return result > 0 ? true : false;
}
alter Procedure [dbo].[GetProtypeList]
@DataBase_InsertUser bigint,
@DataBase_ID bigint
AS
DECLARE @DataBase_Address varchar(50)
DECLARE @DataBase_LoginName varchar(50)
DECLARE @DataBase_DBS varchar(50)
DECLARE @DataBase_Table varchar(50)
DECLARE @DataBase_PassWord varchar(50)
BEGIN
select @DataBase_Address=[DataBase_Address],@DataBase_LoginName=[DataBase_LoginName],
@DataBase_DBS=[DataBase_DBS],@DataBase_Table=[DataBase_Table],
@DataBase_PassWord=[DataBase_PassWord]
FROM DataBaseInfo WHERE [DataBase_DelFlag]=0
AND [DataBase_ID]=@DataBase_ID declare @s varchar(8000)
set @s='INSERT INTO ProtypeInfo SELECT
[PT_ID],'+@DataBase_ID+',[PT_Name],'',[PT_Level],[PT_ParentID],[PT_Unit],[PT_IsShow],[PT_IsGuide],[PT_IsIndex],
[PT_FilterAttribute],[PT_PriceInterval],[PT_StylePath],[PT_KeyWord],[PT_Describe],[PT_Properties],[PT_IsEndType],
[PT_DelFlag],getdate(),'+@DataBase_InsertUser+',[PT_OrderNum],[PT_IsTrue],[PT_S1],[PT_S2],[PT_S3],[PT_S4],[PT_S5]
FROM
DB.PDB.DBO.ProtypeInfo
OPENROWSET
(''sqloledc'','+@DataBase_Address+';'+@DataBase_LoginName+';'+@DataBase_PassWord+','+@DataBase_DBS+'.DBO.'+@DataBase_Table+''')'
exec (@s)
END
@DataBase_DBS=[DataBase_DBS],@DataBase_Table=[DataBase_Table],
@DataBase_PassWord=[DataBase_PassWord]
FROM DataBaseInfo WHERE [DataBase_DelFlag]=0
AND [DataBase_ID]=@DataBase_ID
这个也用动态语言,就是你先把那个set语句个格式