SELECT * FROM dbo.fnc_getcolorsep1_cx(d.fstyleid) x,tcolorgroup a
INNER JOIN dsn_groupitem b ON b.fcolorgroupid=a.fcolorgroupid
INNER JOIN titem c ON c.fitemid=b.fitem
INNER JOIN tstyle d ON d.fstyleid=c.fstyleid
WHERE NOT EXISTS(SELECT 1 FROM dsn_groupitem WHERE fcolorgroupid=a.fcolorgroupid AND rowid<b.rowid) AND a.fseasonid=@fseasonid
这样提示错误:第 1 行: '.' 附近有语法错误。
如果按下面方法调用正常,这个参数该怎么传呢?
SELECT * FROM dbo.fnc_getcolorsep1_cx('205') x,tcolorgroup a
INNER JOIN dsn_groupitem b ON b.fcolorgroupid=a.fcolorgroupid
INNER JOIN titem c ON c.fitemid=b.fitem
INNER JOIN tstyle d ON d.fstyleid=c.fstyleid
WHERE NOT EXISTS(SELECT 1 FROM dsn_groupitem WHERE fcolorgroupid=a.fcolorgroupid AND rowid<b.rowid) AND a.fseasonid=@fseasonid
INNER JOIN dsn_groupitem b ON b.fcolorgroupid=a.fcolorgroupid
INNER JOIN titem c ON c.fitemid=b.fitem
INNER JOIN tstyle d ON d.fstyleid=c.fstyleid
WHERE NOT EXISTS(SELECT 1 FROM dsn_groupitem WHERE fcolorgroupid=a.fcolorgroupid AND rowid<b.rowid) AND a.fseasonid=@fseasonid
这样提示错误:第 1 行: '.' 附近有语法错误。
如果按下面方法调用正常,这个参数该怎么传呢?
SELECT * FROM dbo.fnc_getcolorsep1_cx('205') x,tcolorgroup a
INNER JOIN dsn_groupitem b ON b.fcolorgroupid=a.fcolorgroupid
INNER JOIN titem c ON c.fitemid=b.fitem
INNER JOIN tstyle d ON d.fstyleid=c.fstyleid
WHERE NOT EXISTS(SELECT 1 FROM dsn_groupitem WHERE fcolorgroupid=a.fcolorgroupid AND rowid<b.rowid) AND a.fseasonid=@fseasonid
@fseasonid int,
@fcolorgroupid nchar(10)
AS
SELECT * FROM dbo.fnc_getcolorsep1_cx(d.fstyleid) x,tcolorgroup a
INNER JOIN dsn_groupitem b ON b.fcolorgroupid=a.fcolorgroupid
INNER JOIN titem c ON c.fitemid=b.fitem
INNER JOIN tstyle d ON d.fstyleid=c.fstyleid
WHERE NOT EXISTS(SELECT 1 FROM dsn_groupitem WHERE fcolorgroupid=a.fcolorgroupid AND rowid<b.rowid) AND a.fseasonid=@fseasonidRETURN
GO
select @p = d.fstyleid from tablename where ...
然后用动态SQL,不知道可以吗?
set @tSql = 'SELECT * FROM dbo.fnc_getcolorsep1_cx(' + @p + ') x,tcolorgroup a INNER JOIN dsn_groupitem b ON b.fcolorgroupid=a.fcolorgroupid
INNER JOIN titem c ON c.fitemid=b.fitem
INNER JOIN tstyle d ON d.fstyleid=c.fstyleid
WHERE NOT EXISTS(SELECT 1 FROM dsn_groupitem WHERE fcolorgroupid=a.fcolorgroupid AND rowid<b.rowid) AND a.fseasonid=@fseasonid'exec @tSql动态SQL,测试一下。LZ
比如上面的select语句最后会查出10条的记录,而每条对应的d.fstyleid都可能是不同的值啊。这样是肯定不行的。
dbo.fnc_getcolorsep1_cx(d.fstyleid)此函数的功能是什么么。。
RETURNS TABLE AS
RETURN
(
SELECT DISTINCT TOP 1 k.fcolor as fcolorsep1 FROM tcolor k
INNER join ritemcolor j ON k.fcolorid=j.fcolorid AND j.fsep=1
INNER join titem i ON j.fitemid=i.fitemid
INNER join rgroupcolor l ON l.fColorId=k.fColorId
INNER join tstyle m ON m.fstyleid=i.fstyleid
WHERE m.fstyleid=@fstyleid
)
ALTER PROC dsn_Groupitem_select1_cx
@fseasonid int,
@fcolorgroupid nchar(10)
AS
IF @fcolorgroupid=''
SELECT *,(
SELECT DISTINCT TOP 1 k.fcolor FROM tcolor k
INNER join ritemcolor j ON k.fcolorid=j.fcolorid AND j.fsep=1
INNER join titem i ON j.fitemid=i.fitemid
INNER join rgroupcolor l ON l.fColorId=k.fColorId
WHERE i.fstyleid=d.fstyleid) AS fcolorsep1,(
SELECT DISTINCT TOP 1 p.fcolor FROM tcolor p
INNER join ritemcolor q ON p.fcolorid=q.fcolorid AND q.fsep=2
INNER join titem r ON q.fitemid=r.fitemid
INNER join rgroupcolor s ON s.fColorId=p.fColorId
WHERE r.fstyleid=d.fstyleid) AS fcolorsep2
FROM tcolorgroup a
INNER JOIN dsn_groupitem b ON b.fcolorgroupid=a.fcolorgroupid
INNER JOIN titem c ON c.fitemid=b.fitem
INNER JOIN tstyle d ON d.fstyleid=c.fstyleid
WHERE NOT EXISTS(SELECT 1 FROM dsn_groupitem WHERE fcolorgroupid=a.fcolorgroupid AND rowid<b.rowid) AND a.fseasonid=@fseasonid
ELSE
SELECT *,(
SELECT DISTINCT TOP 1 k.fcolor FROM tcolor k
INNER join ritemcolor j ON k.fcolorid=j.fcolorid AND j.fsep=1
INNER join titem i ON j.fitemid=i.fitemid
INNER join rgroupcolor l ON l.fColorId=k.fColorId
WHERE i.fstyleid=d.fstyleid) AS fcolorsep1,(
SELECT DISTINCT TOP 1 p.fcolor FROM tcolor p
INNER join ritemcolor q ON p.fcolorid=q.fcolorid AND q.fsep=2
INNER join titem r ON q.fitemid=r.fitemid
INNER join rgroupcolor s ON s.fColorId=p.fColorId
WHERE r.fstyleid=d.fstyleid) AS fcolorsep2
FROM tcolorgroup a
INNER JOIN dsn_groupitem b ON b.fcolorgroupid=a.fcolorgroupid
INNER JOIN titem c ON c.fitemid=b.fitem
INNER JOIN tstyle d ON d.fstyleid=c.fstyleid
WHERE a.fcolorgroupid=@fcolorgroupid
ORDER BY b.[default] DESC
RETURN只是我想将fcolorsep1和fcolorsep2两个字段的值通过函数来获得,没想到不行,是否我的思路有问题呢?
lz考虑下,看行不行.