Z
zhousan
Unregistered / Unconfirmed
GUEST, unregistred user!
奇怪的問題,一段SQL語句在查詢分析器中執行可以查出記錄,但寫成存儲過程就無返回記錄了,大家幫看看
===執行這個有50多個記錄===
SELECT a.khbh, a.inv_nbr, a.fprq, a.fpzk, ISNULL(a.ext_amt, 0) AS ext_amt,
(SELECT isnull(SUM(s1.shsl), 0)
FROM songhudanmingxi AS s1
WHERE s1.inv_nbr = a.inv_nbr) AS shsl,
(SELECT mcjc
FROM kehuziliao AS k
WHERE k.khbh = a.khbh) AS khmc,
(SELECT isnull(SUM(s2.zje), 0)
FROM songhudanmingxi AS s2
WHERE s2.inv_nbr = a.inv_nbr) AS zje
FROM SongHuDan a INNER JOIN
id_rep ON a.khbh = id_rep.kh_id
WHERE (a.fprq >= '2005-6-14') AND (a.fprq < '2005-6-25') AND (id_rep.user_id = 'sys')
AND (a.firpwt IS NULL)
================
====寫成的存儲過程====
CREATE PROC inv_total_rep111
@s_date datetime,
@e_date datetime,
@user_id varchar(10)
--@flag int
with ENCRYPTION
as
----------------------????-------------------------------
--if @flag=0
select @s_date,@e_date,@user_id
SELECT a.khbh, a.inv_nbr, a.fprq, a.fpzk, ISNULL(a.ext_amt, 0) AS ext_amt,
(SELECT isnull(SUM(s1.shsl), 0)
FROM songhudanmingxi AS s1
WHERE s1.inv_nbr = a.inv_nbr) AS shsl,
(SELECT mcjc
FROM kehuziliao AS k
WHERE k.khbh = a.khbh) AS khmc,
(SELECT isnull(SUM(s2.zje), 0)
FROM songhudanmingxi AS s2
WHERE s2.inv_nbr = a.inv_nbr) AS zje
FROM SongHuDan a INNER JOIN
id_rep ON a.khbh = id_rep.kh_id
WHERE (a.fprq >= @s_date) AND (a.fprq < @e_date) AND (id_rep.user_id =@user_id)
AND (a.firpwt IS NULL)
ORDER BY a.inv_nbr
===============
執行
inv_total_rep111 '2005-6-14','2005-6-25','sys'
無返回記錄,咋回事呢
===執行這個有50多個記錄===
SELECT a.khbh, a.inv_nbr, a.fprq, a.fpzk, ISNULL(a.ext_amt, 0) AS ext_amt,
(SELECT isnull(SUM(s1.shsl), 0)
FROM songhudanmingxi AS s1
WHERE s1.inv_nbr = a.inv_nbr) AS shsl,
(SELECT mcjc
FROM kehuziliao AS k
WHERE k.khbh = a.khbh) AS khmc,
(SELECT isnull(SUM(s2.zje), 0)
FROM songhudanmingxi AS s2
WHERE s2.inv_nbr = a.inv_nbr) AS zje
FROM SongHuDan a INNER JOIN
id_rep ON a.khbh = id_rep.kh_id
WHERE (a.fprq >= '2005-6-14') AND (a.fprq < '2005-6-25') AND (id_rep.user_id = 'sys')
AND (a.firpwt IS NULL)
================
====寫成的存儲過程====
CREATE PROC inv_total_rep111
@s_date datetime,
@e_date datetime,
@user_id varchar(10)
--@flag int
with ENCRYPTION
as
----------------------????-------------------------------
--if @flag=0
select @s_date,@e_date,@user_id
SELECT a.khbh, a.inv_nbr, a.fprq, a.fpzk, ISNULL(a.ext_amt, 0) AS ext_amt,
(SELECT isnull(SUM(s1.shsl), 0)
FROM songhudanmingxi AS s1
WHERE s1.inv_nbr = a.inv_nbr) AS shsl,
(SELECT mcjc
FROM kehuziliao AS k
WHERE k.khbh = a.khbh) AS khmc,
(SELECT isnull(SUM(s2.zje), 0)
FROM songhudanmingxi AS s2
WHERE s2.inv_nbr = a.inv_nbr) AS zje
FROM SongHuDan a INNER JOIN
id_rep ON a.khbh = id_rep.kh_id
WHERE (a.fprq >= @s_date) AND (a.fprq < @e_date) AND (id_rep.user_id =@user_id)
AND (a.firpwt IS NULL)
ORDER BY a.inv_nbr
===============
執行
inv_total_rep111 '2005-6-14','2005-6-25','sys'
無返回記錄,咋回事呢