我的数据库的表是动态的表,在添加数据的时候有一个字段是DATETIME数据类型的,下面是存储过程
if exists (select * from sysobjects where name='proc_ADD_Order')
drop proc proc_ADD_Order
go
create procedure proc_ADD_Order
@memberID nvarchar(20),
@futuresClass nvarchar(4),
@orderWay nvarchar(10),
@orderSet char(1),
@orderCount int,
@orderPrice nvarchar(20),
@orderTime datetime,
@result char(1),
@penName nvarchar(40),
@txOrderClass nvarchar(10),
@contestID nvarchar(40),
@yyMM nvarchar(4)
as
declare @tablename nvarchar(15),@guid nvarchar(40) set @tablename = 'TxOrder'+@yyMM set @guid=newid()
exec(
'insert into '+@tablename+' ( [guid],[memberid],[futuresclass],[orderway],[orderset],[ordercount],[orderprice],[ordertime],[result],[penname],[txorderclass],[contestid]) values ( '''+@guid+''','''+@memberID+''','''+@futuresClass+''','''+@orderWay+''','''+@orderSet+''','+@orderCount+','+@orderPrice+'+0.00,'+@orderTime+','''+@result+''','''+@penName+''','''+@txOrderClass+''','''+@contestID+''' )'
)
go
下面是我的测试数据:
DECLARE @return_value int EXEC @return_value = [dbo].[proc_ADD_Order]
@memberID = N'ddd',
@futuresClass = N'dd',
@orderWay = N'dsd',
@orderSet = N'w',
@orderCount = 2,
@orderPrice = N'23',
@orderTime = N'2002-08-07 00:00:00.000',
@result = N'd',
@penName = N'dd',
@txOrderClass = N'dd',
@contestID = N'dd',
@yyMM = N'0910'SELECT 'Return Value' = @return_valueGO
结果报错,信息如下:
消息 102,级别 15,状态 1,第 1 行
'7' 附近有语法错误。(1 行受影响)这个里就@orderTime里有7,可是我找了半天也没找到,这是哪里错了啊,还有怎么消息还报一行受影响啊
if exists (select * from sysobjects where name='proc_ADD_Order')
drop proc proc_ADD_Order
go
create procedure proc_ADD_Order
@memberID nvarchar(20),
@futuresClass nvarchar(4),
@orderWay nvarchar(10),
@orderSet char(1),
@orderCount int,
@orderPrice nvarchar(20),
@orderTime datetime,
@result char(1),
@penName nvarchar(40),
@txOrderClass nvarchar(10),
@contestID nvarchar(40),
@yyMM nvarchar(4)
as
declare @tablename nvarchar(15),@guid nvarchar(40) set @tablename = 'TxOrder'+@yyMM set @guid=newid()
exec(
'insert into '+@tablename+' ( [guid],[memberid],[futuresclass],[orderway],[orderset],[ordercount],[orderprice],[ordertime],[result],[penname],[txorderclass],[contestid]) values ( '''+@guid+''','''+@memberID+''','''+@futuresClass+''','''+@orderWay+''','''+@orderSet+''','+@orderCount+','+@orderPrice+'+0.00,'+@orderTime+','''+@result+''','''+@penName+''','''+@txOrderClass+''','''+@contestID+''' )'
)
go
下面是我的测试数据:
DECLARE @return_value int EXEC @return_value = [dbo].[proc_ADD_Order]
@memberID = N'ddd',
@futuresClass = N'dd',
@orderWay = N'dsd',
@orderSet = N'w',
@orderCount = 2,
@orderPrice = N'23',
@orderTime = N'2002-08-07 00:00:00.000',
@result = N'd',
@penName = N'dd',
@txOrderClass = N'dd',
@contestID = N'dd',
@yyMM = N'0910'SELECT 'Return Value' = @return_valueGO
结果报错,信息如下:
消息 102,级别 15,状态 1,第 1 行
'7' 附近有语法错误。(1 行受影响)这个里就@orderTime里有7,可是我找了半天也没找到,这是哪里错了啊,还有怎么消息还报一行受影响啊
drop proc proc_ADD_Order
go
create procedure proc_ADD_Order
@memberID nvarchar(20),
@futuresClass nvarchar(4),
@orderWay nvarchar(10),
@orderSet char(1),
@orderCount int,
@orderPrice nvarchar(20),
@orderTime datetime,
@result char(1),
@penName nvarchar(40),
@txOrderClass nvarchar(10),
@contestID nvarchar(40),
@yyMM nvarchar(4)
as
declare @tablename nvarchar(15),@guid nvarchar(40) set @tablename = 'TxOrder'+@yyMM set @guid=newid()
print
'insert into '
+@tablename
+' ( [guid],[memberid],[futuresclass],[orderway],[orderset],[ordercount],[orderprice],[ordertime],[result],[penname],[txorderclass],[contestid]) values ( '''
+@guid
+''','''
+@memberID
+''','''
+@futuresClass+''','''
+@orderWay+''','''
+@orderSet+''','
+ltrim(@orderCount)
+','
+@orderPrice
+'+0.00,'''
+convert(varchar(19),@orderTime,120)
+''','''
+@result
+''','''
+@penName
+''','''
+@txOrderClass
+''','''
+@contestID
+''' )'
go
将exec改为print..看看输出什么..
消息 245,级别 16,状态 1,过程 proc_ADD_Order,第 16 行
在将 nvarchar 值 'insert into TxOrder0910 ( [guid],[memberid],[futuresclass],[orderway],[orderset],[ordercount],[orderprice],[ordertime],[result],[penname],[txorderclass],[contestid]) values ( '769E73DE-3220-418D-8CD2-A43B9C1AC9DE','ddd','dd','dsd','w',' 转换成数据类型 int 时失败。测试数据:
@memberID = N'ddd',
@futuresClass = N'dd',
@orderWay = N'dsd',
@orderSet = N'w',
@orderCount = 2,
@orderPrice = N'23',
@orderTime = N'2002-08-01 00:00:00.000',
@result = N'd',
@penName = N'dd',
@txOrderClass = N'dd',
@contestID = N'dd',
@yyMM = N'0910'
可是我的是INT 型的啊,这是为什么呢?
参考1楼,因为exec 里面的是字符串,字符串和int不能相加