关于动态添加存储过程的错误
有这么一个存储过程：
该存储过程实现根据传入的@TableNum值，动态的选择数据库的表。然后执行插入工作。一开始时，在如下代码中
values(
'+@RentWay+',
'+@City+',
'+@District+',
'+@Title+',
'+@Address+',
'+@XiaoQu+',
'+UserID+',
'+HouseType+'
在调试时出现了很多意想不到的错误。现在把这个错误贴出，用于提醒自己
1ALTER PROCEDURE [dbo].[ap_BuyAndSaleAction]
2 @DataAction int,
3 @ID int = 0,
4 @RentWay nvarchar(100),
5 @City nvarchar(100),
6 @District nvarchar(100),
7 @Title nvarchar(100),
8 @Address nvarchar(1000),
9 @XiaoQu nvarchar(100),
10-- @AddTime datetime,
11 @UserID int,
12 @HouseType tinyint,
13 @Money money,
14 @Floor tinyint,
15 @TotalFloor tinyint,
16 @MainJi smallint,
17 @NianDai nvarchar(100),
18 @Validity smallint,
19 @ZhongJie tinyint,
20 @ZhuangXiu nvarchar(1000),
21 @PayMent nvarchar(500),
22 @ChaoXiang nvarchar(100),
23 @ChanQuan nvarchar(100),
24 @KaiFaShang nvarchar(100),
25 @Contact nvarchar(100),
26 @Tel varchar(20),
27 @Mobile varchar(20),
28 @Memo nvarchar(max),
29 @Memo1 nvarchar(50),
30 @Memo2 nvarchar(50),
31 @Memo3 nvarchar(50),
32 @Flag tinyint,
33 @TableNum nvarchar(20)
34AS
35if @DataAction=0
36BEGIN
37 declare @select varchar(max)
38 set @select = 'insert into [tb_'+rtrim(@TableNum)+'_BuyAndSale]
39 (
40 [RentWay],
41 [City],
42 [District],
43 [Title],
44 [Address],
45 [XiaoQu],
46 [UserID],
47 [HouseType],
48 [Money],
49 [Floor],
50 [TotalFloor],
51 [MainJi],
52 [NianDai],
53 [Validity],
54 [ZhongJie],
55 [ZhuangXiu],
56 [PayMent],
57 [ChaoXiang],
58 [ChanQuan],
59 [KaiFaShang],
60 [Contact],
61 [Tel],
62 [Mobile],
63 [Memo],
64 [Memo1],
65 [Memo2],
66 [Memo3],
67 [Flag]
68 )
69 values(
70 '''+@RentWay+''',
71 '''+@City+''',
72 '''+@District+''',
73 '''+@Title+''',
74 '''+@Address+''',
75 '''+@XiaoQu+''',
76 '+rtrim(@UserID)+',
77 '+rtrim(@HouseType)+',
78 '+rtrim(@Money)+',
79 '+rtrim(@Floor)+',
80 '+rtrim(@TotalFloor)+',
81 '+rtrim(@MainJi)+',
82 '''+ @NianDai+''',
83 '+rtrim(@Validity)+',
84 '+rtrim(@ZhongJie)+',
85 '''+@ZhuangXiu+''',
86 '''+@PayMent+''',
87 '''+@ChaoXiang+''',
88 '''+@ChanQuan+''',
89 '''+@KaiFaShang+''',
90 '''+@Contact+''',
91 '''+@Tel+''',
92 '''+@Mobile+''',
93 '''+@Memo+''',
94 '''+@Memo1+''',
95 '''+@Memo2+''',
96 '''+@Memo3+''',
97 '+rtrim(@Flag)+')'
98 EXEC(@select)
99 set @ID=scope_identity()
ALTER PROCEDURE [dbo].[ap_BuyAndSaleAction] 2
@DataAction int,3
@ID int = 0,4
@RentWay nvarchar(100),5
@City nvarchar(100),6
@District nvarchar(100),7
@Title nvarchar(100),8
@Address nvarchar(1000),9
@XiaoQu nvarchar(100),10
-- @AddTime datetime,11
@UserID int,12
@HouseType tinyint,13
@Money money,14
@Floor tinyint,15
@TotalFloor tinyint,16
@MainJi smallint,17
@NianDai nvarchar(100),18
@Validity smallint,19
@ZhongJie tinyint,20
@ZhuangXiu nvarchar(1000),21
@PayMent nvarchar(500),22
@ChaoXiang nvarchar(100),23
@ChanQuan nvarchar(100),24
@KaiFaShang nvarchar(100),25
@Contact nvarchar(100),26
@Tel varchar(20),27
@Mobile varchar(20),28
@Memo nvarchar(max),29
@Memo1 nvarchar(50),30
@Memo2 nvarchar(50),31
@Memo3 nvarchar(50),32
@Flag tinyint,33
@TableNum nvarchar(20)34
AS35
if @DataAction=036
BEGIN37
declare @select varchar(max)38
set @select = 'insert into [tb_'+rtrim(@TableNum)+'_BuyAndSale]39
(40
[RentWay],41
[City],42
[District],43
[Title],44
[Address],45
[XiaoQu],46
[UserID],47
[HouseType],48
[Money],49
[Floor],50
[TotalFloor],51
[MainJi],52
[NianDai],53
[Validity],54
[ZhongJie],55
[ZhuangXiu],56
[PayMent],57
[ChaoXiang],58
[ChanQuan],59
[KaiFaShang],60
[Contact],61
[Tel],62
[Mobile],63
[Memo],64
[Memo1],65
[Memo2],66
[Memo3],67
[Flag]68
) 69
values(70
'''+@RentWay+''',71
'''+@City+''',72
'''+@District+''',73
'''+@Title+''',74
'''+@Address+''',75
'''+@XiaoQu+''',76
'+rtrim(@UserID)+',77
'+rtrim(@HouseType)+',78
'+rtrim(@Money)+',79
'+rtrim(@Floor)+',80
'+rtrim(@TotalFloor)+',81
'+rtrim(@MainJi)+',82
'''+ @NianDai+''',83
'+rtrim(@Validity)+',84
'+rtrim(@ZhongJie)+',85
'''+@ZhuangXiu+''',86
'''+@PayMent+''',87
'''+@ChaoXiang+''',88
'''+@ChanQuan+''',89
'''+@KaiFaShang+''',90
'''+@Contact+''',91
'''+@Tel+''',92
'''+@Mobile+''',93
'''+@Memo+''',94
'''+@Memo1+''',95
'''+@Memo2+''',96
'''+@Memo3+''',97
'+rtrim(@Flag)+')'98
EXEC(@select)99
set @ID=scope_identity()该存储过程实现根据传入的@TableNum值，动态的选择数据库的表。然后执行插入工作。一开始时，在如下代码中
1values(
2 '''+@RentWay+''',
3 '''+@City+''',
4 '''+@District+''',
5 '''+@Title+''',
6 '''+@Address+''',
7 '''+@XiaoQu+''',
8 '+rtrim(@UserID)+',
9 '+rtrim(@HouseType)+'
只是写成了：values(2
'''+@RentWay+''',3
'''+@City+''',4
'''+@District+''',5
'''+@Title+''',6
'''+@Address+''',7
'''+@XiaoQu+''',8
'+rtrim(@UserID)+',9
'+rtrim(@HouseType)+'values( '+@RentWay+', '+@City+', '+@District+', '+@Title+', '+@Address+', '+@XiaoQu+', '+UserID+', '+HouseType+'
浙公网安备 33010602011771号