exec 时出错了，求鉴定！

Question

本帖最后由 dayrenlar 于 2014-01-10 11:10:49 编辑

Answer 1

参数随便给的
exec [Order_set] @op_en=0,@totalfee=0.01,
@DiscountFee =0.00,
@AdjustFee =0.00,
@Payment =0.00,
@ModifiedTime ='2014-01-09 11:04:55.000',
@OrderStatus ='WAIT_BUYER_PAY1',
@RefundID ='',
@RefundStatus ='NO_CREATED',
@TradeType ='FIXED',
@SkuID ='01020131105888456123',
@Number =1,
@ProductModel ='',
@Price =0.01,
@TID ='11111111111',
@OrderID  ='222222222222222',
@ProductID   ='3333333333333333',
@CategoryID ='',
@TimeOutAction ='2014-01-10 11:04:55.000',
@BuyerRate =0,
@SellarRate =0,
@BuyerPointFee  =0

Answer 2

根据你的参数，print出来的脚本是这样的：
INSERT into Order_Basicinfo values('0.01','0.00','0.00','0.00','Jan  9 2014 11:04AM','WAIT_PAY','','NO_CREATED','01020131105888456123','1','','0.01','11111111111','222222222222222','3333333333333333','','Jan 10 2014 11:04AM'
,'0','0','0','FIXED')我猜应该是你的表中还有其他列，而你没有对应，导致一些本来是varchar的列，你用numeric插入了，insert into那里写上对应的列名试试

Answer 3

那很明显，很多地方，比如第一列用的是decimal，但是你传'0.01'

Answer 4

那正确的应该怎么写？
用exec @sql

Answer 5

set @sql=
'insert into Order_Basicinfo values('+@TotalFee+','+@DiscountFee+','+@AdjustFee+','+@Payment+','+convert(varchar(38),@ModifiedTime)+','+convert(varchar(38),@OrderStatus)+','+convert(varchar(38),@RefundID)+','
+convert(varchar(38),@RefundStatus)+','+convert(varchar(38),@SkuID)+','+@Number+','+convert(varchar(38),@ProductModel)+','+@Price+','+convert(varchar(38),@TID)+','+convert(varchar(38),@OrderID)+','+convert(varchar(38),@ProductID)+','+convert(varchar(38),@CategoryID)+','
+convert(varchar(38),@TimeOutAction)+
','+@BuyerRate+','+@SellarRate+','+@BuyerPointFee+','+convert(varchar(38),@TradeType)+')';问题照旧

Answer 6

听哥的永远不要 insert into Order_Basicinfo values(值....)
写insert into Order_Basicinfo(字段....) values(值....)

Answer 7

USE [Remote_Orders]
GO
/****** Object:  Table [dbo].[Order_Basicinfo]    Script Date: 01/10/2014 12:30:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Order_Basicinfo](
[TotalFee] [decimal](18, 2) NOT NULL,
[DiscountFee] [decimal](18, 2) NOT NULL,
[AdjustFee] [decimal](18, 2) NOT NULL,
[Payment] [decimal](18, 2) NOT NULL,
[ModifiedTime] [datetime] NOT NULL,
[OrderStatus] [varchar](50) NOT NULL,
[RefundID] [varchar](50) NOT NULL,
[RefundStatus] [varchar](30) NULL,
[SkuID] [varchar](50) NOT NULL,
[Number] [int] NOT NULL,
[ProductModel] [varchar](500) NOT NULL,
[Price] [decimal](18, 2) NOT NULL,
[TID] [varchar](50) NOT NULL,
[OrderID] [varchar](50) NOT NULL,
[ProductID] [varchar](50) NOT NULL,
[CategoryID] [varchar](2000) NULL,
[TimeOutAction] [datetime] NOT NULL,
[BuyerRate] [bit] NOT NULL,
[SellarRate] [bit] NOT NULL,
[BuyerPointFee] [int] NOT NULL,
[TradeType] [varchar](30) NOT NULL,
 CONSTRAINT [PK_Order_Basicinfo] PRIMARY KEY NONCLUSTERED 
(
[OrderID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [ps_Time]([ModifiedTime])
GO
SET ANSI_PADDING OFF
GO

Answer 8

但是我没改动的情况下不抱错哦，但是你给的建表脚本怎么对数据类型都加中括号，让我怀疑你是不是有哪些数据库配置有问题--CREATE TABLE [dbo].[Order_Basicinfo](
--    [TotalFee] decimal(18, 2) NOT NULL,
--    [DiscountFee] decimal(18, 2) NOT NULL,
--    [AdjustFee] decimal(18, 2) NOT NULL,
--    [Payment] decimal(18, 2) NOT NULL,
--    [ModifiedTime] datetime NOT NULL,
--    [OrderStatus] varchar(50) NOT NULL,
--    [RefundID] varchar(50) NOT NULL,
--    [RefundStatus] varchar(30) NULL,
--    [SkuID] varchar(50) NOT NULL,
--    [Number] int NOT NULL,
--    [ProductModel] varchar(500) NOT NULL,
--    [Price] decimal(18, 2) NOT NULL,
--    [TID] varchar(50) NOT NULL,
--    [OrderID] varchar(50) NOT NULL,
--    [ProductID] varchar(50) NOT NULL,
--    [CategoryID] varchar(2000) NULL,
--    [TimeOutAction] datetime NOT NULL,
--    [BuyerRate] bit NOT NULL,
--    [SellarRate] bit NOT NULL,
--    [BuyerPointFee] INT NOT NULL,
--    [TradeType] varchar(30) NOT NULL,
-- CONSTRAINT [PK_Order_Basicinfo] PRIMARY KEY NONCLUSTERED 
--(
--    [OrderID] ASC
--)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
--)
ALTER proc[dbo].[Order_set]
(
@Op_en int=null,
@TotalFee decimal (18,2)=null,
@DiscountFee decimal(18,2)=null,
@AdjustFee decimal (18,2)=null,
@Payment decimal (18,2)=null,
@ModifiedTime datetime =null,
@OrderStatus varchar(50)=null,
@RefundID varchar(50)=null,
@RefundStatus varchar(30)=null,
@TradeType varchar(20)=null,
@SkuID varchar(50)=null,
@Number int=null,
@ProductModel varchar(500)=null,
@Price decimal (18,2)=null,
@TID varchar(50)=null,
@OrderID varchar(50) =null,
@ProductID  varchar(50) =null,
@CategoryID varchar(50)=null,
@TimeOutAction datetime=null,
@BuyerRate bit=null,
@SellarRate bit=null,
@BuyerPointFee int =null,
@ObjectItems varchar(2000)=null
)
AS
if(@op_en=0)
begin
declare @sql varchar(2000)
set @sql =
'insert into Order_Basicinfo values('''+convert(varchar(30),@TotalFee)+''','''+convert(varchar(30),@DiscountFee)+''','''+convert(varchar(30),@AdjustFee)+''','''+convert(varchar(30),@Payment)+''','''+convert(varchar(30),@ModifiedTime)+''','''+@OrderStatus+''','''+@RefundID+''','''
+@RefundStatus+''','''+@SkuID+''','''+convert(varchar(9),@Number)+''','''+@ProductModel+''','''+convert(varchar(30),@Price)+''','''+@TID+''','''+@OrderID+''','''+@ProductID+''','''+@CategoryID+''','''
+cast(@TimeOutAction as varchar)+'''
,'''+convert(varchar(1),@BuyerRate)+''','''+convert(varchar(1),@SellarRate)+''','''+convert(varchar(9),@BuyerPointFee)+''','''+@TradeType+''')';
EXEC  (@sql)
endexec [Order_set] @op_en=0,@totalfee=0.01,
@DiscountFee =0.00,
@AdjustFee =0.00,
@Payment =0.00,
@ModifiedTime ='2014-01-09 11:04:55.000',
@OrderStatus='WAIT_PAY',
@RefundID ='',
@RefundStatus ='NO_CREATED',
@TradeType ='FIXED',
@SkuID ='01020131105888456123',
@Number =1,
@ProductModel ='',
@Price =0.01,
@TID ='11111111111',
@OrderID  ='222222222222222',
@ProductID   ='3333333333333333',
@CategoryID ='',
@TimeOutAction ='2014-01-10 11:04:55.000',
@BuyerRate =0,
@SellarRate =0,
@BuyerPointFee  =0SELECT * FROM Order_Basicinfo/*
TotalFee                                DiscountFee                             AdjustFee                               Payment                                 ModifiedTime            OrderStatus                                        RefundID                                           RefundStatus                   SkuID                                              Number      ProductModel                                                                                                                                                                                                                                                     Price                                   TID                                                OrderID                                            ProductID                                          CategoryID                                                                                                                                                                                                                                                       TimeOutAction           BuyerRate SellarRate BuyerPointFee TradeType
--------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ----------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------ -------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- --------- ---------- ------------- ------------------------------
0.01                                    0.00                                    0.00                                    0.00                                    2014-01-09 11:04:00.000 WAIT_PAY                                                                                              NO_CREATED                     01020131105888456123                               1                                                                                                                                                                                                                                                                            0.01                                    11111111111                                        222222222222222                                    3333333333333333                                                                                                                                                                                                                                                                                                    2014-01-10 11:04:00.000 0         0          0             FIXED*/

Answer 9

建议以后你要得到插入一个表的语句的时候直接在这个表上点右键，然后用生成脚本，自动生成insert的语句，这样就能保证字段及其位置不错了。