存储过程返回数据集
CREATE PROCEDURE Proc_QueryOnlineStock
@PartName varchar(100)=NULL,
@PartNo varchar(100)=NULL,
@PartCode varchar(100)=NULL,
@EquType int=0
AS
/************************************************************************************************************
DESCRIPTION: 查询在库数量
PARAMETER: @PartName 默认参数为NULL,@PartNo 默认参数为NULL,@PartCode 默认参数为NULL,@EquType 默认参数为0
CREATE_DATE:2011/01/11
CREATE_AUTHOR:Haiqi Wang
Exp:
EXEC QueryOnlineStock NULL,NULL,NULL,0默认值问题
*************************************************************************************************************/
IF@PartName IS NOT NULL AND @PartNo IS NOT NULL--当PartName与PartNo不为空时
BEGIN
SELECT dbo.tb_PartName.PartName, dbo.tb_PartNo.PartNo, dbo.tb_PartCode.PartCode,
dbo.tb_EquType.EquType,dbo.tb_EquNo.EquNO,dbo.tb_Stock.OnlineNum, dbo.tb_Position.Position
FROM dbo.tb_EquNo INNER JOIN
dbo.tb_Stock ON dbo.tb_EquNo.ID = dbo.tb_Stock.EquNo INNER JOIN
dbo.tb_EquOEM ON dbo.tb_Stock.EquOEM = dbo.tb_EquOEM.ID INNER JOIN
dbo.tb_EquType ON dbo.tb_Stock.EquType = dbo.tb_EquType.ID INNER JOIN
dbo.tb_Position ON dbo.tb_Stock.Position = dbo.tb_Position.ID INNER JOIN
dbo.tb_PartCode ON dbo.tb_Stock.PartCode = dbo.tb_PartCode.id INNER JOIN
dbo.tb_PartNo ON dbo.tb_Stock.PartNo = dbo.tb_PartNo.id INNER JOIN
dbo.tb_PartName ON dbo.tb_Stock.PartName = dbo.tb_PartName.id
WHERE dbo.tb_Stock.PartName=(SELECT id FROM tb_PartName WHERE dbo.tb_PartName.PartName=@PartName) AND
dbo.tb_Stock.PartNo=(SELECT id FROM dbo.tb_PartNo WHERE dbo.tb_PartNo.PartNo=@PartNo)
RETURN
END
IF@PartName IS NOT NULL AND @PartCode IS NOT NULL--当PartName与PartCode不为空时
BEGIN
SELECT dbo.tb_PartName.PartName, dbo.tb_PartNo.PartNo, dbo.tb_PartCode.PartCode,
dbo.tb_EquType.EquType,dbo.tb_EquNo.EquNO,dbo.tb_Stock.OnlineNum, dbo.tb_Position.Position
FROM dbo.tb_EquNo INNER JOIN
dbo.tb_Stock ON dbo.tb_EquNo.ID = dbo.tb_Stock.EquNo INNER JOIN
dbo.tb_EquOEM ON dbo.tb_Stock.EquOEM = dbo.tb_EquOEM.ID INNER JOIN
dbo.tb_EquType ON dbo.tb_Stock.EquType = dbo.tb_EquType.ID INNER JOIN
dbo.tb_Position ON dbo.tb_Stock.Position = dbo.tb_Position.ID INNER JOIN
dbo.tb_PartCode ON dbo.tb_Stock.PartCode = dbo.tb_PartCode.id INNER JOIN
dbo.tb_PartNo ON dbo.tb_Stock.PartNo = dbo.tb_PartNo.id INNER JOIN
dbo.tb_PartName ON dbo.tb_Stock.PartName = dbo.tb_PartName.id
WHERE dbo.tb_Stock.PartName=(SELECT id FROM tb_PartName WHERE dbo.tb_PartName.PartName=@PartName) AND
dbo.tb_Stock.PartCode=(SELECT id FROM dbo.tb_PartCode WHERE dbo.tb_PartCode.PartCode=@PartCode)
RETURN
END
IF@PartName IS NOT NULL--当PartName不为空时
BEGIN
SELECT dbo.tb_PartName.PartName, dbo.tb_PartNo.PartNo, dbo.tb_PartCode.PartCode,
dbo.tb_EquType.EquType,dbo.tb_EquNo.EquNO,dbo.tb_Stock.OnlineNum, dbo.tb_Position.Position
FROM dbo.tb_EquNo INNER JOIN
dbo.tb_Stock ON dbo.tb_EquNo.ID = dbo.tb_Stock.EquNo INNER JOIN
dbo.tb_EquOEM ON dbo.tb_Stock.EquOEM = dbo.tb_EquOEM.ID INNER JOIN
dbo.tb_EquType ON dbo.tb_Stock.EquType = dbo.tb_EquType.ID INNER JOIN
dbo.tb_Position ON dbo.tb_Stock.Position = dbo.tb_Position.ID INNER JOIN
dbo.tb_PartCode ON dbo.tb_Stock.PartCode = dbo.tb_PartCode.id INNER JOIN
dbo.tb_PartNo ON dbo.tb_Stock.PartNo = dbo.tb_PartNo.id INNER JOIN
dbo.tb_PartName ON dbo.tb_Stock.PartName = dbo.tb_PartName.id
WHERE dbo.tb_Stock.PartName=(SELECT id FROM tb_PartName WHERE dbo.tb_PartName.PartName=@PartName)
RETURN
END
IF@PartNo IS NOT NULL--当PartNo不为空时
BEGIN
SELECT dbo.tb_PartName.PartName, dbo.tb_PartNo.PartNo, dbo.tb_PartCode.PartCode,
dbo.tb_EquType.EquType,dbo.tb_EquNo.EquNO,dbo.tb_Stock.OnlineNum, dbo.tb_Position.Position
FROM dbo.tb_EquNo INNER JOIN
dbo.tb_Stock ON dbo.tb_EquNo.ID = dbo.tb_Stock.EquNo INNER JOIN
dbo.tb_EquOEM ON dbo.tb_Stock.EquOEM = dbo.tb_EquOEM.ID INNER JOIN
dbo.tb_EquType ON dbo.tb_Stock.EquType = dbo.tb_EquType.ID INNER JOIN
dbo.tb_Position ON dbo.tb_Stock.Position = dbo.tb_Position.ID INNER JOIN
dbo.tb_PartCode ON dbo.tb_Stock.PartCode = dbo.tb_PartCode.id INNER JOIN
dbo.tb_PartNo ON dbo.tb_Stock.PartNo = dbo.tb_PartNo.id INNER JOIN
dbo.tb_PartName ON dbo.tb_Stock.PartName = dbo.tb_PartName.id
WHERE dbo.tb_Stock.PartNo=(SELECT id FROM dbo.tb_PartNo WHERE dbo.tb_PartNo.PartNo=@PartNo)
RETURN
END
IF@PartCode IS NOT NULL--当PartCode不为空时
BEGIN
SELECT dbo.tb_PartName.PartName, dbo.tb_PartNo.PartNo, dbo.tb_PartCode.PartCode,
dbo.tb_EquType.EquType,dbo.tb_EquNo.EquNO,dbo.tb_Stock.OnlineNum, dbo.tb_Position.Position
FROM dbo.tb_EquNo INNER JOIN
dbo.tb_Stock ON dbo.tb_EquNo.ID = dbo.tb_Stock.EquNo INNER JOIN
dbo.tb_EquOEM ON dbo.tb_Stock.EquOEM = dbo.tb_EquOEM.ID INNER JOIN
dbo.tb_EquType ON dbo.tb_Stock.EquType = dbo.tb_EquType.ID INNER JOIN
dbo.tb_Position ON dbo.tb_Stock.Position = dbo.tb_Position.ID INNER JOIN
dbo.tb_PartCode ON dbo.tb_Stock.PartCode = dbo.tb_PartCode.id INNER JOIN
dbo.tb_PartNo ON dbo.tb_Stock.PartNo = dbo.tb_PartNo.id INNER JOIN
dbo.tb_PartName ON dbo.tb_Stock.PartName = dbo.tb_PartName.id
WHERE dbo.tb_Stock.PartCode=(SELECT id FROM dbo.tb_PartCode WHERE dbo.tb_PartCode.PartCode=@PartCode)
RETURN
END
IF@EquType <>0--当设备类型不为空时
BEGIN
SELECT dbo.tb_PartName.PartName, dbo.tb_PartNo.PartNo, dbo.tb_PartCode.PartCode,
dbo.tb_EquType.EquType,dbo.tb_EquNo.EquNO,dbo.tb_Stock.OnlineNum, dbo.tb_Position.Position
FROM dbo.tb_EquNo INNER JOIN
dbo.tb_Stock ON dbo.tb_EquNo.ID = dbo.tb_Stock.EquNo INNER JOIN
dbo.tb_EquOEM ON dbo.tb_Stock.EquOEM = dbo.tb_EquOEM.ID INNER JOIN
dbo.tb_EquType ON dbo.tb_Stock.EquType = dbo.tb_EquType.ID INNER JOIN
dbo.tb_Position ON dbo.tb_Stock.Position = dbo.tb_Position.ID INNER JOIN
dbo.tb_PartCode ON dbo.tb_Stock.PartCode = dbo.tb_PartCode.id INNER JOIN
dbo.tb_PartNo ON dbo.tb_Stock.PartNo = dbo.tb_PartNo.id INNER JOIN
dbo.tb_PartName ON dbo.tb_Stock.PartName = dbo.tb_PartName.id
WHERE dbo.tb_Stock.EquType=@EquType
RETURN
END
GO
//为啥这个C# 调用的时候没有返回的记录呢?
EXEC Proc_QueryOnlineStock @PartName='SERNER'
这样可以看到记录呢???
为什么 ????
[解决办法]
