奇怪的問題,一段SQL語句在查詢分析器中執行可以查出記錄,但寫成存儲過程就無返回記錄了,大家幫看看 ( 积分: 100 )

  • 主题发起人 主题发起人 zhousan
  • 开始时间 开始时间
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'
無返回記錄,咋回事呢
 
奇怪的問題,一段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'
無返回記錄,咋回事呢
 
把 @s_date datetime,
@e_date datetime,
改為
@s_date varchar(10),
@e_date varchar(10,
試試
 
一樣的,沒有效果
 
我不用參數了,直接從查詢分析器COPY過來了,還是不行。。見鬼了真是
=====================================
alter PROC inv_total_rep111
-- @s_date varchar(10),
-- @e_date varchar(10),
-- @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 >= '2005-06-14') AND (a.fprq < '2005-06-25') AND (id_rep.user_id ='sys')
AND (a.firpwt IS NULL)
ORDER BY a.inv_nbr
=====================
 
加上
set ansi_nulls off
试试
 
偶重啟服務器,現在好象可以了
 
后退
顶部