一下存储过程在SQL中执行完全没有问题
可是.NET程序用DataSet 接受不到值···············
为什么??
该怎么做?
ALTER PROC [dbo].[PUR_rptVenderW]
@VenderId varchar(16),
@DateTime DATETIME---------------日期年部分
AS
BEGIN
DECLARE @weekDay varchar(30)
DECLARE @SQL varchar(MAX)
DECLARE @Count int
SET @Count = 1
SET @SQL = ''IF @VenderId = ''------------------如果没有输入供应商ID
BEGIN
WHILE @Count <= 7
BEGIN
SET @weekDay = dateadd(day,@Count - datepart(dw,@DateTime),@DateTime)
SET @SQL = @SQL + ' SELECT a.VenderId,ISNULL(sum(b.Amt),0.00) as Amt
FROM STN_InStockHD as a,STN_InStockDet as b
WHERE b.InStockNo = a.InStockNo
AND a.InStockDate = '''+@weekDay+'''
GROUP BY a.VenderID UNION ALL'
SET @Count = @Count + 1
END
SET @SQL = substring(@SQL,0,LEN(@SQL)-8)
CREATE TABLE #temp(venderId varchar(16),Amt float)
insert into #temp exec(@SQL)
;with Week as
(Select VenderId,row_Number() over(order by getdate()) as RID,
Amt from #temp )
select VenderId id,
ISNULL(SUM(case when (RID-1) % 7 = 0 then Amt end),0.00) as Sun,
ISNULL(SUM(case when (RID-1) % 7 = 1 then Amt end),0.00) as Mon,
ISNULL(SUM(case when (RID-1) % 7 = 2 then Amt end),0.00) as Tue,
ISNULL(SUM(case when (RID-1) % 7 = 3 then Amt end),0.00) as Wed,
ISNULL(SUM(case when (RID-1) % 7 = 4 then Amt end),0.00) as Thu,
ISNULL(SUM(case when (RID-1) % 7 = 5 then Amt end),0.00) as Fri,
ISNULL(SUM(case when (RID-1) % 7 = 6 then Amt end),0.00) as Sat,
SUM(Amt) as Amt from Week GROUP BY venderId
END
else
BEGIN
WHILE @Count <= 7
BEGIN
SET @weekDay = dateadd(day,@Count - datepart(dw,@DateTime),@DateTime)
SET @SQL = @SQL + ' SELECT ISNULL(sum(Amt),0.00) as Amt
FROM STN_InStockDet
WHERE InStockNo IN(SELECT InStockNo
FROM STN_InStockHD
WHERE VenderId = '''+@VenderId+'''
AND InStockDate = '''+@weekDay+''') UNION ALL'
SET @Count = @Count + 1
END
SET @SQL = substring(@SQL,0,LEN(@SQL)-8)
CREATE TABLE #tempe(Amt float)
insert into #tempe exec(@SQL)
;with Week as
(Select row_Number() over(order by getdate()) as RID,
Amt from #tempe
)
select @VenderId AS id,
ISNULL(SUM(case when (RID-1) % 7 = 0 then Amt end),0.00) as Sun,
ISNULL(SUM(case when (RID-1) % 7 = 1 then Amt end),0.00) as Mon,
ISNULL(SUM(case when (RID-1) % 7 = 2 then Amt end),0.00) as Tue,
ISNULL(SUM(case when (RID-1) % 7 = 3 then Amt end),0.00) as Wed,
ISNULL(SUM(case when (RID-1) % 7 = 4 then Amt end),0.00) as Thu,
ISNULL(SUM(case when (RID-1) % 7 = 5 then Amt end),0.00) as Fri,
ISNULL(SUM(case when (RID-1) % 7 = 6 then Amt end),0.00) as Sat,
SUM(amt) Amt from Week
END
END
可是.NET程序用DataSet 接受不到值···············
为什么??
该怎么做?
ALTER PROC [dbo].[PUR_rptVenderW]
@VenderId varchar(16),
@DateTime DATETIME---------------日期年部分
AS
BEGIN
DECLARE @weekDay varchar(30)
DECLARE @SQL varchar(MAX)
DECLARE @Count int
SET @Count = 1
SET @SQL = ''IF @VenderId = ''------------------如果没有输入供应商ID
BEGIN
WHILE @Count <= 7
BEGIN
SET @weekDay = dateadd(day,@Count - datepart(dw,@DateTime),@DateTime)
SET @SQL = @SQL + ' SELECT a.VenderId,ISNULL(sum(b.Amt),0.00) as Amt
FROM STN_InStockHD as a,STN_InStockDet as b
WHERE b.InStockNo = a.InStockNo
AND a.InStockDate = '''+@weekDay+'''
GROUP BY a.VenderID UNION ALL'
SET @Count = @Count + 1
END
SET @SQL = substring(@SQL,0,LEN(@SQL)-8)
CREATE TABLE #temp(venderId varchar(16),Amt float)
insert into #temp exec(@SQL)
;with Week as
(Select VenderId,row_Number() over(order by getdate()) as RID,
Amt from #temp )
select VenderId id,
ISNULL(SUM(case when (RID-1) % 7 = 0 then Amt end),0.00) as Sun,
ISNULL(SUM(case when (RID-1) % 7 = 1 then Amt end),0.00) as Mon,
ISNULL(SUM(case when (RID-1) % 7 = 2 then Amt end),0.00) as Tue,
ISNULL(SUM(case when (RID-1) % 7 = 3 then Amt end),0.00) as Wed,
ISNULL(SUM(case when (RID-1) % 7 = 4 then Amt end),0.00) as Thu,
ISNULL(SUM(case when (RID-1) % 7 = 5 then Amt end),0.00) as Fri,
ISNULL(SUM(case when (RID-1) % 7 = 6 then Amt end),0.00) as Sat,
SUM(Amt) as Amt from Week GROUP BY venderId
END
else
BEGIN
WHILE @Count <= 7
BEGIN
SET @weekDay = dateadd(day,@Count - datepart(dw,@DateTime),@DateTime)
SET @SQL = @SQL + ' SELECT ISNULL(sum(Amt),0.00) as Amt
FROM STN_InStockDet
WHERE InStockNo IN(SELECT InStockNo
FROM STN_InStockHD
WHERE VenderId = '''+@VenderId+'''
AND InStockDate = '''+@weekDay+''') UNION ALL'
SET @Count = @Count + 1
END
SET @SQL = substring(@SQL,0,LEN(@SQL)-8)
CREATE TABLE #tempe(Amt float)
insert into #tempe exec(@SQL)
;with Week as
(Select row_Number() over(order by getdate()) as RID,
Amt from #tempe
)
select @VenderId AS id,
ISNULL(SUM(case when (RID-1) % 7 = 0 then Amt end),0.00) as Sun,
ISNULL(SUM(case when (RID-1) % 7 = 1 then Amt end),0.00) as Mon,
ISNULL(SUM(case when (RID-1) % 7 = 2 then Amt end),0.00) as Tue,
ISNULL(SUM(case when (RID-1) % 7 = 3 then Amt end),0.00) as Wed,
ISNULL(SUM(case when (RID-1) % 7 = 4 then Amt end),0.00) as Thu,
ISNULL(SUM(case when (RID-1) % 7 = 5 then Amt end),0.00) as Fri,
ISNULL(SUM(case when (RID-1) % 7 = 6 then Amt end),0.00) as Sat,
SUM(amt) Amt from Week
END
END
ALTER PROC [dbo].[PUR_rptVenderW]
@VenderId varchar(16),
@DateTime DATETIME---------------日期年部分
AS
set nocount on --加上这句
前要加GO。
建议用表变量Declare @t table(..),就不会有这样的问题了。
在存储过程开始时加
set nocount on在最后Select前加
set nocount off
可能我加的位置不对·····
现在改成这样,结果还是不行(SQL查询分析器里面OK)-----------------------供應商--------------------------------------------
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'PUR_RPTVenderW')
DROP PROCEDURE PUR_RPTVenderW
GO
----------------周---------------------
CREATE PROC PUR_rptVenderW
@VenderId varchar(16),
@DateTime DATETIME---------------日期年部分
AS
set nocount on --加上这句
BEGIN
DECLARE @weekDay varchar(30)
DECLARE @SQL varchar(MAX)
DECLARE @Count int
SET @Count = 1
SET @SQL = ''IF @VenderId = ''------------------如果没有输入供应商ID
BEGIN
WHILE @Count <= 7
BEGIN
SET @weekDay = dateadd(day,@Count - datepart(dw,@DateTime),@DateTime)
SET @SQL = @SQL + ' SELECT a.VenderId,ISNULL(sum(b.Amt),0.00) as Amt
FROM STN_InStockHD as a,STN_InStockDet as b
WHERE b.InStockNo = a.InStockNo
AND a.InStockDate = '''+@weekDay+'''
GROUP BY a.VenderID UNION ALL'
SET @Count = @Count + 1
END
SET @SQL = substring(@SQL,0,LEN(@SQL)-8)
CREATE TABLE #temp(venderId varchar(16),Amt float)
insert into #temp exec(@SQL)
;with Week as
(Select VenderId,row_Number() over(order by getdate()) as RID,
Amt from #temp )
select VenderId id,
ISNULL(SUM(case when (RID-1) % 7 = 0 then Amt end),0.00) as Sun,
ISNULL(SUM(case when (RID-1) % 7 = 1 then Amt end),0.00) as Mon,
ISNULL(SUM(case when (RID-1) % 7 = 2 then Amt end),0.00) as Tue,
ISNULL(SUM(case when (RID-1) % 7 = 3 then Amt end),0.00) as Wed,
ISNULL(SUM(case when (RID-1) % 7 = 4 then Amt end),0.00) as Thu,
ISNULL(SUM(case when (RID-1) % 7 = 5 then Amt end),0.00) as Fri,
ISNULL(SUM(case when (RID-1) % 7 = 6 then Amt end),0.00) as Sat,
SUM(Amt) as Amt from Week GROUP BY venderId
ENDelse
BEGIN
WHILE @Count <= 7
BEGIN
--星期日
SET @weekDay = dateadd(day,@Count - datepart(dw,@DateTime),@DateTime)
SET @SQL = @SQL + ' SELECT ISNULL(sum(Amt),0.00) as Amt
FROM STN_InStockDet
WHERE InStockNo IN(SELECT InStockNo
FROM STN_InStockHD
WHERE VenderId = '''+@VenderId+'''
AND InStockDate = '''+@weekDay+''') UNION ALL'
SET @Count = @Count + 1
END
set nocount off
SET @SQL = substring(@SQL,0,LEN(@SQL)-8)
exec(@SQL) --select * from #tempe
-- ;with Week as
-- (Select row_Number() over(order by getdate()) as RID,
-- Amt from #tempe
-- )
-- select @VenderId AS id,
-- ISNULL(SUM(case when (RID-1) % 7 = 0 then Amt end),0.00) as Sun,
-- ISNULL(SUM(case when (RID-1) % 7 = 1 then Amt end),0.00) as Mon,
-- ISNULL(SUM(case when (RID-1) % 7 = 2 then Amt end),0.00) as Tue,
-- ISNULL(SUM(case when (RID-1) % 7 = 3 then Amt end),0.00) as Wed,
-- ISNULL(SUM(case when (RID-1) % 7 = 4 then Amt end),0.00) as Thu,
-- ISNULL(SUM(case when (RID-1) % 7 = 5 then Amt end),0.00) as Fri,
-- ISNULL(SUM(case when (RID-1) % 7 = 6 then Amt end),0.00) as Sat,
-- SUM(amt) Amt from Week
END
END
GOEXEC PUR_rptVenderW 'k001','2009-7-9'