这个是我的存储过程
USE [OriginDB]
GO
/****** 对象: StoredProcedure [UnitMoving].[InsertPrdMoveToPrdStoreANDUpdate] 脚本日期: 03/28/2008 14:59:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--=====================================================================
--作者:hzj
--时间:2008-3-26 10:22:34
--描述
/*
数据表 [UnitMoving.PrdMoveToPrdStore] 添加
*/
--=====================================================================ALTER PROCEDURE [UnitMoving].[InsertPrdMoveToMtrStoreANDUpdate]
(
@PrdMoveToMtrStoreID bigint out,
@PrdReturnID bigint,
@PrdMoveToMtrStoreCode varchar (50) ,
@PrdMoveToMtrStoreTypeID bigint ,
@PrdXItemSpecID bigint ,
@PrdLotNo varchar (50) ,
@PrdQuantity decimal (18,2) ,
@MtrID bigint ,
@MtrLotNo varchar (50) ,
@MtrUnitPrice money,
@PrdMoveDate datetime ,
@DataInputMachine varchar (50) ,
@Location varchar (50) ,
@Memo varchar (50)
)
AS
BEGIN
declare @a int ,@b int ,@c int,@d int
begin Transaction exec UnitMoving.InsertPrdMoveToMtrStore @PrdMoveToMtrStoreID,@PrdMoveToMtrStoreCode,@PrdMoveToMtrStoreTypeID,@PrdXItemSpecID,@PrdLotNo,@PrdQuantity,@MtrID,@MtrLotNo,@PrdMoveDate,@DataInputMachine,@Location,@Memo
set @a=@@error
declare @postfix varchar(10)
set @postfix=dbo.F_GetMonthSerialCode(getdate())
declare @CurTablename varchar(50)
declare @CONSname varchar(50)
set @CurTablename='Inventory.MtrInventory'+@postfix
set @CONSname='MtrInventory'+@postfix
IF OBJECT_ID (@CurTablename, 'U') IS NULL
begin
exec Inventory.BuidlPrdInvertory
set @b=@@error
end
declare @sqls Nvarchar(max)
declare @rut bigint
--查询有没有PrdLotNo
set @sqls='select @rut=count(*) from '+@CurTablename+' WHERE MtrLotNo='''+@MtrLotNo+''''
declare @param Nvarchar(max)
set @param=N'@MtrLotNo varchar(50),@rut bigint output'
exec sp_executesql @sqls,@param,@MtrLotNo,@rut output
--没有,插入
if @rut=0
begin
declare @MtrExpireDate datetime
--declare @Location varchar(50),@PrdMoveDate datetime
select @PrdMoveDate=PrdMoveDate,@Location=Location from UnitMoving.PrdMoveToMtrStore where MtrLotNo=@MtrLotNo
set @MtrExpireDate=dateadd(month,Material.F_GetMaterialMtrValidityMonth(@MtrID),@PrdMoveDate)
exec Inventory.InsertMtrInventory @MtrID,@MtrLotNo,@PrdMoveDate,@PrdQuantity,@MtrExpireDate,@MtrUnitPrice,@Location
end
else
begin
set @sqls='
UPDATE '+@CurTablename+'
SET
MtrInventoryInitQty =MtrInventoryInitQty+@PrdQuantity
where MtrLotNo=@MtrLotNo'
declare @param2 nvarchar(max)
set @param2=N'@MtrLotNo varchar(50),@PrdQuantity decimal (18,2)'
exec sp_executesql @sqls,@param2,@MtrLotNo,@PrdQuantity
end
set @c=@@error
exec Sale.UpdatePrdReturn @PrdReturnID
set @d=@@error
if @a<>0 or @b<>0 or @c<>0 or @d<>0
begin
print ' 操作失败!!'
Rollback transaction
end
else
commit transaction
END////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
下面是报的错误
消息 214,级别 16,状态 3,过程 sp_executesql,第 1 行
过程需要类型为 'ntext/nchar/nvarchar' 的参数 '@parameters'。大家帮忙看看错在哪啦? 实在是想不出来哪有错呀?
还有这个错误 通常是什么意思呀?
USE [OriginDB]
GO
/****** 对象: StoredProcedure [UnitMoving].[InsertPrdMoveToPrdStoreANDUpdate] 脚本日期: 03/28/2008 14:59:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--=====================================================================
--作者:hzj
--时间:2008-3-26 10:22:34
--描述
/*
数据表 [UnitMoving.PrdMoveToPrdStore] 添加
*/
--=====================================================================ALTER PROCEDURE [UnitMoving].[InsertPrdMoveToMtrStoreANDUpdate]
(
@PrdMoveToMtrStoreID bigint out,
@PrdReturnID bigint,
@PrdMoveToMtrStoreCode varchar (50) ,
@PrdMoveToMtrStoreTypeID bigint ,
@PrdXItemSpecID bigint ,
@PrdLotNo varchar (50) ,
@PrdQuantity decimal (18,2) ,
@MtrID bigint ,
@MtrLotNo varchar (50) ,
@MtrUnitPrice money,
@PrdMoveDate datetime ,
@DataInputMachine varchar (50) ,
@Location varchar (50) ,
@Memo varchar (50)
)
AS
BEGIN
declare @a int ,@b int ,@c int,@d int
begin Transaction exec UnitMoving.InsertPrdMoveToMtrStore @PrdMoveToMtrStoreID,@PrdMoveToMtrStoreCode,@PrdMoveToMtrStoreTypeID,@PrdXItemSpecID,@PrdLotNo,@PrdQuantity,@MtrID,@MtrLotNo,@PrdMoveDate,@DataInputMachine,@Location,@Memo
set @a=@@error
declare @postfix varchar(10)
set @postfix=dbo.F_GetMonthSerialCode(getdate())
declare @CurTablename varchar(50)
declare @CONSname varchar(50)
set @CurTablename='Inventory.MtrInventory'+@postfix
set @CONSname='MtrInventory'+@postfix
IF OBJECT_ID (@CurTablename, 'U') IS NULL
begin
exec Inventory.BuidlPrdInvertory
set @b=@@error
end
declare @sqls Nvarchar(max)
declare @rut bigint
--查询有没有PrdLotNo
set @sqls='select @rut=count(*) from '+@CurTablename+' WHERE MtrLotNo='''+@MtrLotNo+''''
declare @param Nvarchar(max)
set @param=N'@MtrLotNo varchar(50),@rut bigint output'
exec sp_executesql @sqls,@param,@MtrLotNo,@rut output
--没有,插入
if @rut=0
begin
declare @MtrExpireDate datetime
--declare @Location varchar(50),@PrdMoveDate datetime
select @PrdMoveDate=PrdMoveDate,@Location=Location from UnitMoving.PrdMoveToMtrStore where MtrLotNo=@MtrLotNo
set @MtrExpireDate=dateadd(month,Material.F_GetMaterialMtrValidityMonth(@MtrID),@PrdMoveDate)
exec Inventory.InsertMtrInventory @MtrID,@MtrLotNo,@PrdMoveDate,@PrdQuantity,@MtrExpireDate,@MtrUnitPrice,@Location
end
else
begin
set @sqls='
UPDATE '+@CurTablename+'
SET
MtrInventoryInitQty =MtrInventoryInitQty+@PrdQuantity
where MtrLotNo=@MtrLotNo'
declare @param2 nvarchar(max)
set @param2=N'@MtrLotNo varchar(50),@PrdQuantity decimal (18,2)'
exec sp_executesql @sqls,@param2,@MtrLotNo,@PrdQuantity
end
set @c=@@error
exec Sale.UpdatePrdReturn @PrdReturnID
set @d=@@error
if @a<>0 or @b<>0 or @c<>0 or @d<>0
begin
print ' 操作失败!!'
Rollback transaction
end
else
commit transaction
END////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
下面是报的错误
消息 214,级别 16,状态 3,过程 sp_executesql,第 1 行
过程需要类型为 'ntext/nchar/nvarchar' 的参数 '@parameters'。大家帮忙看看错在哪啦? 实在是想不出来哪有错呀?
还有这个错误 通常是什么意思呀?
消息 214,级别 16,状态 3,过程 sp_executesql,第 1 行
过程需要类型为 'ntext/nchar/nvarchar' 的参数 '@parameters'。
----------------------------------------------------------
写的很清楚了,参见联机丛书,sp_executeSQL的参数要求为n系列的。 ntext,nvarchar,nchar.
set @sqls='select @rut=count(*) from '+@CurTablename+' WHERE MtrLotNo='''+@MtrLotNo+''''
declare @param Nvarchar(max)
set @param=N'@MtrLotNo varchar(50),@rut bigint output'
exec sp_executesql @sqls,@param,@MtrLotNo,@rut output
--更改为:--查询有没有PrdLotNo
set @sqls='select @r=count(*) from '+@CurTablename+' WHERE MtrLotNo='''+@MtrLotNo+''''
exec sp_executesql @sqls,N'@r bigint output',@rut output
类似的错误太多。下班了。回家。 。
exec sp_executesql @sql,N'@num int output',@num output set @s=N'@num int output'
exec sp_executesql @sql,@s,@num output
测试一会,好象不支持变量格式的