select query with two fields info in UDF

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 2005 Forums

Transact-SQL (2005)

select query with two fields info in UDF

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-03-30 : 13:39:35

Is it possible to get "filepath" info based on this join TAB_Recipients.docid = tab_repository.docid

docid is an int field.

And show the result as hyphen seperated "Name - Filepath"

I have this user defined function just showing "name" want to add the above filepath field to show result "Name - filepath"


Alter FUNCTION [dbo].[getRecipientsInfo](@ModuleID integer, @ModuleName VARCHAR(10))
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @s VARCHAR(8000)
	SET @s=''
	SELECT @s=@s + [Name] + char(13)
	from TAB_Recipients where ModuleRecordID = @ModuleID and ModuleName = @ModuleName and RecipientType = 'TO'
	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 helpful info.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-30 : 13:41:52

quote:
Originally posted by cplusplus

Is it possible to get "filepath" info based on this join TAB_Recipients.docid = tab_repository.docid

docid is an int field.

And show the result as hyphen seperated "Name - Filepath"

I have this user defined function just showing "name" want to add the above filepath field to show result "Name - filepath"

Alter FUNCTION [dbo].[getRecipientsInfo](@ModuleID integer, @ModuleName VARCHAR(10))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @s VARCHAR(8000)
SET @s=''
SELECT @s=@s + [Name] + '-' + filepath +char(13)
from TAB_Recipients where ModuleRecordID = @ModuleID and ModuleName = @ModuleName and RecipientType = 'TO'
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 helpful info.

modify as above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-03-30 : 13:48:34

Hello Visakh, Thanks.

No, The filepath info is in table tab_repository.

it is not in table tab_recipients.

"Name" field is in tab_recipients
"Filepath" field is in tab_repository

common field between both table is docid

Thank you.

quote:
Originally posted by visakh16

quote:
Originally posted by cplusplus

Is it possible to get "filepath" info based on this join TAB_Recipients.docid = tab_repository.docid

docid is an int field.

And show the result as hyphen seperated "Name - Filepath"

I have this user defined function just showing "name" want to add the above filepath field to show result "Name - filepath"

Alter FUNCTION [dbo].[getRecipientsInfo](@ModuleID integer, @ModuleName VARCHAR(10))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @s VARCHAR(8000)
SET @s=''
SELECT @s=@s + [Name] + '-' + filepath +char(13)
from TAB_Recipients where ModuleRecordID = @ModuleID and ModuleName = @ModuleName and RecipientType = 'TO'
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 helpful info.

modify as above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-30 : 13:52:43

[code]
Alter FUNCTION [dbo].[getRecipientsInfo](@ModuleID integer, @ModuleName VARCHAR(10))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @s VARCHAR(8000)
SET @s=''
SELECT @s=@s + r.[Name] + '-' + rp.filepath +char(13)
from TAB_Recipients r
join tab_repository rp
on rp.docid = r.docid
where r.ModuleRecordID = @ModuleID and r.ModuleName = @ModuleName and r.RecipientType = 'TO'
If @s >''
BEGIN
SELECT @s = left(@s, len(@s)-1)
END
ELSE
BEGIN
SELECT @s = ''
END
Return @s
END
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources