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 [email protected]'
END
IF @SubCategory<>''
BEGIN
IF @where IS NOT NULL
BEGIN
SET @[email protected]+N' AND [email protected] AND [email protected]'
END
ELSE
BEGIN
SET @where=N' WHERE [email protected] AND [email protected]'
END
END
IF @Type<>'All'
BEGIN
IF @where IS NOT NULL
BEGIN
SET @[email protected]+N' AND [email protected]'
END
ELSE
BEGIN
SET @where=N' WHERE [email protected]'
END
END
IF @FromDate IS NOT NULL
BEGIN
IF @where IS NOT NULL
BEGIN
SET @[email protected]+ N' AND a.LastEditDate >=''' + RTRIM(@FromDate) + ' 00:00:00'''
SET @[email protected]+ N' AND a.LastEditDate <=''' + RTRIM(@ToDate) + ' 23:59:59'''
END
ELSE
BEGIN
SET @where=N' WHERE a.LastEditDate >=''' + RTRIM(@FromDate) + ' 00:00:00'''
SET @[email protected]+N' AND a.LastEditDate <=''' + RTRIM(@ToDate) + ' 23:59:59'''
END
END
SET @sql = @[email protected]
END
EXEC SP_EXECUTESQL @sql,[email protected] CHAR(15),
@SubCategory INT,
@Manufacturer INT,
@Type CHAR (10),
@FromDate DATETIME,
@ToDate DATETIME',
@ItemNumber,
@SubCategory,
@Manufacturer,
@Type,
@FromDate,
@ToDate
------解决方案--------------------
传的参数明显过多.
------解决方案--------------------
EXEC SP_EXECUTESQL @sql,[email protected] 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个参数，如果在查询分析器里面能顺利执行的话，检查一下你调用存储过程的程序吧，看看什么地方少传了参数，或者多穿了参数