日期:2014-05-18  浏览次数:20411 次

高手请进:调用这个存储过程的时候,老是说传的参数不对
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个参数,如果在查询分析器里面能顺利执行的话,检查一下你调用存储过程的程序吧,看看什么地方少传了参数,或者多穿了参数