where中使用变量的问题
我想在视图中使用变量,下面的写法不对请教应如何写?
declare @yf varchar(20)
--set @yf=201203
SELECT a.月份, a.退货后回款 AS hk, a.本年回款, a.zyid, a.编码
FROM dbo.gzlj a INNER JOIN
(SELECT zyid, MAX(月份) 月份
FROM gzlj
WHERE 月份 < @yf GROUP BY zyid) b ON a.zyid = b.zyid AND a.月份 = b.月份
WHERE (a.本年回款 > 0)
[解决办法]
CREATE FUNCTION F_VIEW(@yf varchar(20))
RETURNS TABLE
AS
RETURN (SELECT a.月份, a.退货后回款 AS hk, a.本年回款, a.zyid, a.编码
FROM dbo.gzlj a INNER JOIN
(SELECT zyid, MAX(月份) 月份
FROM gzlj
WHERE 月份 < @yf GROUP BY zyid) b ON a.zyid = b.zyid AND a.月份 = b.月份
WHERE (a.本年回款 > 0))
GO
SELECT * FROM DBO.F_VIEW('201203')
