I hope you can help me out with this one, I always get great answers in this site, so this time I come with a weird thing that I found on a SQL script, I'll explain: I intend to accumulate the whole valor of a column from a query, and when I run the script alone it works perfect, however whenever I try to use the same script within a function or a stored procedure, my variable gets the valor of the last record. This is my script:
DECLARE @loSetTable TABLE (members VARCHAR(MAX), sorter INT)
declare @lsEnlarge varchar(max)
set @lsEnlarge=''
SELECT @lsEnlarge=@lsEnlarge +'[menu_item].[h].&[' + rtrim(item_id) + '],'
FROM cat_objective_item m1
INNER JOIN cat_menu_item m2 ON (m1.item_id = m2.menu_item_id AND m2.db_id = 2)
WHERE objective_id IN (6)
ORDER BY objective_id,item_id
set @lsEnlarge = substring(@lsEnlarge,1,len(@lsEnlarge)-1)
INSERT INTO @loSetTable VALUES ('SET itemgroup1 AS ''' + @lsEnlarge + ' '' ' ,1)
SELECT * FROM @loSetTable
And the output is fine:
SET itemgroup1 AS '[menu_item].[h].&[013388],[menu_item].[h].&[015783],[menu_item].[h].&[016049],[menu_item].[h].&[016097],[menu_item].[h].&[016464],[menu_item].[h].&[017802],[menu_item].[h].&[018255],[menu_item].[h].&[018282],[menu_item].[h].&[10150],[menu_item].[h].&[10151] '
But when I put the SAME script in a function:
ALTER FUNCTION [dbo].[mk_mmx_fn_get_kfc_mdx_members] ()
RETURNS @loSetTable TABLE (members VARCHAR(MAX), sorter INT)
AS
BEGIN
DECLARE @lsEnlarge AS VARCHAR(MAX)
SET @lsEnlarge=''
--First group of items
SELECT @lsEnlarge=@lsEnlarge +'[menu_item].[h].&[' + rtrim(item_id) + '],'
FROM cat_objective_item m1
INNER JOINcat_menu_item m2 ON (m1.item_id = m2.menu_item_id AND m2.db_id = 2)
WHERE objective_id IN (6)
ORDER BY objective_id,item_id
set @lsEnlarge = substring(@lsEnlarge,1,len(@lsEnlarge)-1)
INSERT INTO @loSetTable VALUES ('SET itemgroup1 AS ''' + @lsEnlarge + '''' ,1)
RETURN
END
I'm getting this:
SET itemgroup1 AS '[menu_item].[h].&[10151]'
As you can see, Im only getting the last item (which is in the last row) I tried using a store procedure and the result is the same, do you know why this is happening?? Thanks!
