inline functions - SQL Server Forums

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)

inline functions

Author

Topic

badinar
Starting Member

14 Posts

Posted - 2008-09-26 : 13:09:13

i'm trying to fill a col of a table with comma seperated values from another table. the below works but,

is there a way to replace this user defined function with an inline sql statement.

update product p
set p.searchtext = p.shortdesc + ' ' + fn_GetAttributeValues(p.id)

CREATE FUNCTION [dbo].[fn_GetAttributeValues]
(
@productId INT
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @str VARCHAR(MAX); SET @str = ''

SELECT @str = @str + av.Value + ','
FROM dbo.AttributeValue av,
WHERE av.ProductId = @productId
RETURN @str
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 13:19:57

[code]update p
set p.searchtext =left(vl.vallist,len(vl.vallist)-1)
from product p
cross apply (select Value + ',' AS [text()]
FROM dbo.AttributeValue
WHERE ProductId = p.ProductId
FOR XML PATH(''))vl(vallist)[/code]