高手请进：调用这个存储过程的时候，老是说传的参数不对
CREATE PROCEDURE AA
@ItemNumber CHAR(15),
@SubCategory INT ,
@Manufacturer INT,
@Type CHAR (10),
@FromDate CHAR(25),
@ToDate CHAR(25)
AS
DECLARE @sql NVARCHAR(4000)
DECLARE @where NVARCHAR(500)
BEGIN
SET @sql =N'SELECT a.TransactionNumber,
a.Reason,
Item=a.ItemNumber,
Description=b.descrip,
Manufacturer=d.Manufactory,
Category=c.Description,
Type=a.Operation,
AttchFlag=ISNULL(e.AttachmentID,''0''),
a.LastEditUser,
a.LastEditDate
FROM dbo.IM_MapPriceLockLog a WITH (NOLOCK)
LEFT JOIN dbo.arinvt01 b WITH (NOLOCK)
ON a.ItemNumber=b.item
INNER JOIN dbo.ItemCatalog c WITH (NOLOCK)
ON a.ItemCategory=c.Catalog
INNER JOIN dbo.manufactory d WITH (NOLOCK)
ON a.Manufacturer=d.Code
LEFT JOIN dbo.IM_IMAttachment e WITH (NOLOCK)
ON a.TransactionNumber=e.ObjectNumber'
IF @ItemNumber<>''
BEGIN
SET @where=N' WHERE a.ItemNumber =@ItemNumber'
END
IF @SubCategory<>''
BEGIN
IF @where IS NOT NULL
BEGIN
SET @where=@where+N' AND a.ItemCategory=@SubCategory AND a.Manufacturer=@Manufacturer'
END
ELSE
BEGIN
SET @where=N' WHERE a.ItemCategory=@SubCategory AND a.Manufacturer=@Manufacturer'
END
END
IF @Type<>'All'
BEGIN
IF @where IS NOT NULL
BEGIN
SET @where=@where+N' AND a.Operation=@Type'
END
ELSE
BEGIN
SET @where=N' WHERE a.Operation=@Type'
END
END
IF @FromDate IS NOT NULL
BEGIN
IF @where IS NOT NULL
BEGIN
SET @where=@where+ N' AND a.LastEditDate >=''' + RTRIM(@FromDate) + ' 00:00:00'''
SET @where=@where+ N' AND a.LastEditDate <=''' + RTRIM(@ToDate) + ' 23:59:59'''
END
ELSE
BEGIN
SET @where=N' WHERE a.LastEditDate >=''' + RTRIM(@FromDate) + ' 00:00:00'''
SET @where=@where+N' AND a.LastEditDate <=''' + RTRIM(@ToDate) + ' 23:59:59'''
END
END
SET @sql = @sql+@where
END
EXEC SP_EXECUTESQL @sql,N'@ItemNumber CHAR(15),
@SubCategory INT,
@Manufacturer INT,
@Type CHAR (10),
@FromDate DATETIME,
@ToDate DATETIME',
@ItemNumber,
@SubCategory,
@Manufacturer,
@Type,
@FromDate,
@ToDate
[解决办法]
传的参数明显过多.
[解决办法]
EXEC SP_EXECUTESQL @sql,N'@ItemNumber CHAR(15),
@SubCategory INT,
@Manufacturer INT,
@Type CHAR (10),
@FromDate DATETIME,
@ToDate DATETIME',
@ItemNumber,
@SubCategory,
@Manufacturer,
@Type,
@FromDate,
@ToDate
－－－－
你的过程哪有这么多的参数呀
[解决办法]
1、第一点
@ItemNumber CHAR(15),
@SubCategory INT ,
@Manufacturer INT,
@Type CHAR (10),
@FromDate CHAR(25),
@ToDate CHAR(25)
这个存储过程，你调用的时候，只需要传以上这6个参数，如果在查询分析器里面能顺利执行的话，检查一下你调用存储过程的程序吧，看看什么地方少传了参数，或者多穿了参数
2、你调用存储过程的时候，明显给的参数太多了，你给了10几个参数，那肯定不行
EXEC SP_EXECUTESQL @sql,N'@ItemNumber CHAR(15),
@SubCategory INT,
@Manufacturer INT,
@Type CHAR (10),
@FromDate DATETIME,
@ToDate DATETIME',
@ItemNumber,
@SubCategory,
@Manufacturer,
@Type,
@FromDate,
@ToDate
3、调用存储过程的时候，不能声明变量类型的，你在以上的调用方法就明显是错误的
[解决办法]
好多。。。。。狂汗。。。。。
尝试一下3楼建议。。。。
太多了。。。。
[解决办法]
EXEC SP_EXECUTESQL @sql,N'@ItemNumber CHAR(15),
@SubCategory INT,
@Manufacturer INT,
@Type CHAR (10),
@FromDate DATETIME,
@ToDate DATETIME',
@ItemNumber,
@SubCategory,
@Manufacturer,
@Type,
@FromDate,
@ToDate
------------------------------------
1、@sql,
2、N'@ItemNumber CHAR(15),@SubCategory INT, @Manufacturer INT,@Type CHAR (10),@FromDate DATETIME, @ToDate DATETIME',
3、@ItemNumber,
4、@SubCategory,
5、@Manufacturer,
6、@Type,
7、@FromDate,
8、@ToDate
你的存储过程只要6个参数，你给了8个，是不是过多呀。
