创建临时表用来组装数据

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[DZFP_BJ_DZFP_BPJG]
@FPXM_ID int,--反拍项目ID
@USER_ID int--当前登录用户
as
begin
--创建临时表用来组装数据
declare @bjTable table (
 CGSP_ID INT PRIMARY KEY,
 SPMC NVARCHAR(255),
 JLDW NVARCHAR(255),
 ZGDJXJ INT,
 JHQ DATETIME,
 PM   NVARCHAR(255),
 CGSL INT,
 BPSCJG decimal(24, 3),
 ZFCGJ decimal(24, 3),
 ZKL   INT,
 FPGZ_ID INT,
 GYS_ID INT,
 GYSMC NVARCHAR(255)
)

--声明 采购项目Id
DECLARE @CGXM_ID INT;
SET @CGXM_ID=(SELECT CGXM_ID FROM DZFP_FPXM WHERE FPXM_ID=@FPXM_ID )
--声明反拍规则Id
DECLARE @FPGZ_ID INT
SET @FPGZ_ID=(SELECT FPGZ_ID FROM DZFP_FPGZ WHERE CGXM_ID=@CGXM_ID )

--声明供应商ID和供应商
DECLARE @GYS_ID INT,@GYSMC NVARCHAR(255)
--SELECT *FROM DZFP_GYSBM WHERE USER_ID=@USER_ID AND CGXM_ID=@CGXM_ID
print @user_id
print @cgxm_id
SET @GYS_ID=(SELECT GYS_ID FROM DZFP_GYSBM WHERE USER_ID=@USER_ID AND CGXM_ID=@CGXM_ID )
SET @GYSMC=(SELECT GYSMC FROM DZFP_GYSBM WHERE USER_ID=@USER_ID AND CGXM_ID=@CGXM_ID )
--声明游标
DECLARE SP_CURSOR CURSOR FOR
SELECT CGSP_ID,SPMC,JLDW,ZGDJXJ,JHQ,PM,CGSL FROM DZFP_CGSP WHERE CGXM_ID=@CGXM_ID

--打开游标
OPEN SP_CURSOR
--声明 变量
DECLARE
 @CGSP_ID INT,
 @SPMC NVARCHAR(255),
 @JLDW NVARCHAR(255),
 @ZGDJXJ INT,
 @JHQ DATETIME,
 @PM   NVARCHAR(255),
 @CGSL INT,
 @BPSCJG decimal(24, 3),
 @ZFCGJ decimal(24, 3),
 @ZKL   INT
FETCH NEXT FROM SP_CURSOR INTO @CGSP_ID,@SPMC,@JLDW,@ZGDJXJ,@JHQ,@PM,@CGSL
WHILE @@FETCH_STATUS = 0
BEGIN
  --@BPSCJG,@ZFCGJ,@ZKL
    --SELECT * FROM DZFP_BPJG
    --市场价格
    SET @BPSCJG=(SELECT CASE WHEN MIN(BPSCJG) IS NULL THEN '0' ELSE MIN(BPSCJG) END BPSCJG FROM DZFP_BPJG WHERE CGSP_ID=@CGSP_ID )
    --政府采购价
    SET @ZFCGJ=(SELECT CASE WHEN MIN(ZFCGJ) IS NULL THEN '0' ELSE MIN(ZFCGJ) END ZFCGJ FROM DZFP_BPJG WHERE CGSP_ID=@CGSP_ID )
    --折扣率
    SET @ZKL=(SELECT CASE WHEN MIN(ZKL) IS NULL THEN '0' ELSE MIN(ZKL)END ZKL FROM DZFP_BPJG WHERE CGSP_ID=@CGSP_ID )
 insert into @bjTable values(@CGSP_ID,@SPMC,@JLDW,@ZGDJXJ,@JHQ,@PM,@CGSL,@BPSCJG,@ZFCGJ,@ZKL, @FPGZ_ID,@GYS_ID,@GYSMC)
 FETCH NEXT FROM SP_CURSOR INTO @CGSP_ID,@SPMC,@JLDW,@ZGDJXJ,@JHQ,@PM,@CGSL
END
CLOSE SP_CURSOR
DEALLOCATE SP_CURSOR
SELECT CGSP_ID,SPMC,JLDW,ZGDJXJ,JHQ,PM,CGSL,BPSCJG,ZFCGJ ZFCGJ_LS,ZKL ZKL_LS,FPGZ_ID,GYS_ID,GYSMC FROM @bjTable

end
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO