where中使用变量的问题

[dfwfs]

我想在视图中使用变量,下面的写法不对请教应如何写?

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)

[jyh070207]

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')

[dfwfs]

[Quote=引用 4 楼 的回复:]
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 月份……
[/Quote]

成功!万分感谢!

[唐诗三百首]

declare @yf varchar(20)
--set @yf=201203
exec('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)')

[jyh070207]

视图不能带参数,可以创建一个返回内嵌表值的用户定义函数来替代视图

[dfwfs]

[Quote=引用 2 楼 的回复:]
视图不能带参数,可以创建一个返回内嵌表值的用户定义函数来替代视图
[/Quote]

我是新人,能否详细说说