This is the function definition:

create function fn_getmgcli (@cli int,@mkt int)
RETURNS @margins TABLE (marketcode int primary key, mg_factor int, shrmgfactor int)

AS
BEGIN

IF EXISTS(SELECT marketcode FROM tradlive.dbo.marketmargin WHERE marketcode=@mkt AND clientcode=@cli)
INSERT @margins
SELECT marketcode,mg_factor,shrmgfactor FROM tradlive.dbo.marketmargin
WHERE marketcode=@mkt AND clientcode=@cli
ELSE
BEGIN
IF EXISTS(SELECT marketcode FROM tradlive.dbo.marketmargin
WHERE marketcode=@mkt AND companycode=(SELECT companycode FROM tradlive.dbo.clientmaster
WHERE clientcode=@cli))
INSERT @margins
SELECT marketcode,mg_factor,shrmgfactor
FROM tradlive.dbo.marketmargin
WHERE marketcode=@mkt AND companycode=(SELECT companycode FROM tradlive.dbo.clientmaster
WHERE clientcode=@cli)
ELSE
INSERT @margins
SELECT marketcode,mg_factor,shrmgfactor FROM tradlive.dbo.marketmargin
WHERE marketcode=@mkt AND companycode=1
END
RETURN
END


and this is part of the SELECT statement calling it:


SELECT ...

FROM tradlive.dbo.dealmaster d WITH (NOLOCK) JOIN tradlive.dbo.marketmaster m WITH (NOLOCK) ON d.marketcode=m.marketcode
JOIN tradlive.dbo.marketprice mp ON d.marketcode=mp.marketcode
JOIN tradlive.dbo.marketdate md ON d.marketcode=md.marketcode
JOIN tradlive.dbo.marketdesc mt ON d.marketcode=mt.marketcode
JOIN fn_getmgcli(@clientcode,d.marketcode) mg ON mg.marketcode=d.marketcode
JOIN tradlive.dbo.marketspread ms ON d.marketcode=ms.marketcode
JOIN tradlive.dbo.currencies y ON d.currcode=y.currcode
JOIN tradlive.dbo.clientmaster cm ON d.clientcode=cm.clientcode
JOIN tradlive.dbo.mktgroup g WITH (NOLOCK) ON g.groupcode=m.groupcode
...


The problem is that the column in the function call is prefixed 'd.' and SQL doesn't like doing that. I am currently converting the code to use a cursor to process the rows one by one, but that isn't really very clever.

Simon