User defined function select query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

User defined function select query

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2006-06-02 : 13:56:22

I have used the user defined functions with one field without joins, now i need to use it with two fields with a join.

I am getting an error invalid field RF.FileName
and CN.type
The fields are there in the table. Can you please help how i can make the join in this case.

*****************************************************************
CREATE FUNCTION dbo.ccsFunctionRepositoryInfo(@ModuleID integer)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @s VARCHAR(8000)
SET @s=''
SELECT @s=@s + [RF.FileName] + ' - ' + [CN.type] + ','
from TAB_ccsRF RF, Tab_CCSCN CN where RF.ModuleRecordID = @ModuleID and RF.ModuleRecordID = CN.ModuleRecordID
If @s IS NOT NULL AND @s <> ''
BEGIN
SELECT @s = left(@s, Datalength(@s)-1)
END
ELSE
BEGIN
SELECT @s = ''
END
Return @s
END

***********************************************

Thank you very much for the information.

Kristen
Test

22859 Posts

Posted - 2006-06-02 : 14:11:35

SELECT @s=@s + RF.[FileName] + ' - ' + CN.[type] + ','

This is more easily done as:


CREATE FUNCTION dbo.ccsFunctionRepositoryInfo(@ModuleID integer)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @s VARCHAR(8000)
SELECT @s = COALESCE(@s+',', '')
          + RF.[FileName] + ' - ' + CN.[type]
FROM  dbo.TAB_ccsRF AS RF
      JOIN dbo.Tab_CCSCN AS CN
           ON CN.ModuleRecordID = RF.ModuleRecordID
WHERE RF.ModuleRecordID = @ModuleID
-- You probably should have an ORDER BY here for consistency / repeatability

RETURN COALESCE(@s, '')
END

Kristen

reddymade
Posting Yak Master

165 Posts

Posted - 2006-06-02 : 16:09:54

Thank you very much - Kristen.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources