这个问题困扰了我很久，网上也参考了几篇文章，一直没解决。
先说更新，这个容易一些：存储过程如下：
1
ALTER proc proc_UpdatePtoPinfoAndPdetail
2(
3@tableinfo varchar(20),@tabledetail varchar(20),@GoodsName nvarchar(30),
4@BranchID int,@GoodsCate int,
5@MarketPrices decimal,@StockPrice decimal,@SuppliersID varchar(50),
6@GoodsOrigin nvarchar(20), @GoodsService nvarchar(30),
7@GoodsTime varchar(50), @GoodsParameters nvarchar(1000),
8@GoodsCount int,@GoodsReturn nvarchar(1000),
9@GoodsImage0 varchar(20),@GoodsID int
10)
11
12As
13begin transaction
14
15exec('update '+@tableinfo+' set GoodsName='+@GoodsName+',BranchID='+@BranchID+',GoodsOrigin='+@GoodsOrigin+
16',GoodsService='+@GoodsService+',GoodsTimeToM='+@GoodsTime+',GoodsReturn='+@GoodsReturn+
17',GoodsParameters='+@GoodsParameters+',GoodsInventory='+@GoodsCount+' where GoodsID='+@GoodsID)
18
19exec('update '+@tabledetail+' set MarketPrices='+@MarketPrices+',StockPrice='+@StockPrice+'
20,GoodsImg0='+@GoodsImage0+' where GoodsID='+@GoodsID)
21
22COMMIT TRANSACTION
23
语法没错误，可以建立成功。在执行的时候到Goodsname的时候若是输入int型，则插入成功，否则报错：
服务器: 消息 207，级别 16，状态 3，行 1
列名 'asd' 无效。
（所影响的行数为 4 行）
不知道如何解决，请大家指点指点～
插入的：
1ALTER proc proc_InsertPtoPinfoAndPdetail
2(
3@tableinfo varchar(20),@tabledetail varchar(20),
4@TopTreeID int,
5@GoodsName nvarchar(30),@GoodsBrand nvarchar(20),@BranchID int,@SuppliersID varchar(50),
6@MarketPrices decimal,@StockPrice decimal,@GoodsOrigin nvarchar(20),
7@GoodsService nvarchar(30),@GoodsTime varchar(50), @GoodsParameters nvarchar(1000),
8@GoodsCount int,@GoodsReturn nvarchar(1000),@GoodsImg0 varchar(20),
9@GoodsID int output
10)
11
12As
13begin transaction
14declare @sqlstr varchar(4000)
15set @sqlstr=
16'Insert '+ @tableinfo+'(TopTreeID,BranchID,GoodsName,GoodsBrand,SuppliersID,GoodsOrigin,GoodsService,
17GoodsTimeToM,GoodsReturn,GoodsParameters,GoodsInventory) values
18('+CONVERT(nvarchar,@TopTreeID)+','+CONVERT(nvarchar,@BranchID)+','+@GoodsName+','+@GoodsBrand+','+@SuppliersID+','+@GoodsOrigin+','+@GoodsService+','+
19@GoodsTime+','+@GoodsReturn+','+@GoodsParameters+','+CONVERT(nvarchar,@GoodsCount)+')'
20exec(@sqlstr)
21select @GoodsID=@@identity
22
23declare @sqlstr1 varchar(4000)
24set @sqlstr1=
25'insert '+ @tabledetail+'(GoodsID,MarketPrices,StockPrice,GoodsImg0)
26values('+@GoodsID+','+@MarketPrices+','+@StockPrice+','+@GoodsImg0+')'
27exec(@sqlstr1)
28COMMIT TRANSACTION
说明：对主子表进行插入，主键为Goodsid自增长，所以在插入子表的时候必须获取主表的系统分配ID，用了一个全局变量@@identity获取
这段代码的数据转换部分我还不敢确定是否正确，如果不进行转换，则跟更新报一样的错误，但是转换也会报错：
（所影响的行数为 1 行）
服务器: 消息 245，级别 16，状态 1，过程 proc_InsertPtoPinfoAndPdetail，行 24
将 varchar 值 'insert goodsdetail_book(GoodsID,MarketPrices,StockPrice,GoodsImg0)
values(' 转换为数据类型为 int 的列时发生语法错误。
似乎将整句当成一个字符串处理。
走过路过的高手多谢指教啊～
08年3月20日
贴上正确的插入
1ALTER proc proc_InsertPtoPinfoAndPdetail
2(
3@tableinfo varchar(20),@tabledetail varchar(20),
4@TopTreeID int,@GoodsIsNull int,
5@GoodsName nvarchar(30),@GoodsBrand nvarchar(20),@BranchID int,@SuppliersID varchar(50),
6@MarketPrices decimal,@StockPrice decimal,@GoodsOrigin nvarchar(20),@GoodsDetail nvarchar(1000),
7@GoodsService nvarchar(30),@GoodsTime varchar(50), @GoodsParameters nvarchar(1000),
8@GoodsCount int,@GoodsReturn nvarchar(1000),@GoodsImg0 varchar(100),@GoodsImg1 varchar(100),
9@GoodsImg2 varchar(100),@GoodsImg3 varchar(100),@GoodsImg4 varchar(100),@SmallImage varchar(100),
10@GoodsID nvarchar(20) output
11)
12
13As
14begin transaction
15
16exec('Insert '+ @tableinfo+'(TopTreeID,BranchID,GoodsName,GoodsBrand,SuppliersID,GoodsOrigin,GoodsService,
17GoodsTimeToM,GoodsReturn,GoodsParameters,GoodsInventory,GoodsDetails) values
18('+@TopTreeID+','+@BranchID+',"'+@GoodsName+'","'+@GoodsBrand+'","'+@SuppliersID+'","'+@GoodsOrigin+
19'","'+@GoodsService+'","'+@GoodsTime+'","'+@GoodsReturn+'","'+@GoodsParameters+'","'+@GoodsCount+'","'+@GoodsDetail+'")')
20
21select @GoodsID=@@identity
22if(@GoodsImg0 is not null)
23begin
24 set @GoodsImg0 = convert(nvarchar(10),@GoodsID)+'_01.'+@GoodsImg0
25set @SmallImage=convert(nvarchar(10),@GoodsID)+'_Themb.'+@GoodsImg0
26end
27if(@GoodsImg1 is not null)
28begin
29 set @GoodsImg1 = convert(nvarchar(10),@GoodsID)+'_02.'+@GoodsImg1
30end
31if(@GoodsImg2 is not null)
32begin
33 set @GoodsImg2 = convert(nvarchar(10),@GoodsID)+'_03.'+@GoodsImg2
34end
35if(@GoodsImg3 is not null)
36begin
37 set @GoodsImg3 = convert(nvarchar(10),@GoodsID)+'_04.'+@GoodsImg3
38end
39if(@GoodsImg4 is not null)
40begin
41 set @GoodsImg4 = convert(nvarchar(10),@GoodsID)+'_05.'+@GoodsImg4
42end
43
44exec('insert '+ @tabledetail+'(GoodsID,BranchID,MarketPrices,StockPrice,GoodsIsNull,GoodsImg0,GoodsImg1,GoodsImg2,GoodsImg3,GoodsImg4,GoodsImg0Small)
45values("'+@GoodsID+'",'+@BranchID+','+@MarketPrices+','+@StockPrice+','+@GoodsIsNull+',"'+@GoodsImg0+'","'+@GoodsImg1+'","'+@GoodsImg2+'","'+@GoodsImg3+'","'+@GoodsImg4+'","'+@SmallImage+'")')
46COMMIT TRANSACTION
47
posted @ 2008-01-24 14:39
二嘎 阅读(116)
评论(3) 编辑 收藏