quote:

---

Originally posted by stan_the_man21ph

Hi,

I have this code snippet:

CREATE FUNCTION [dbo].[fnGetSCACMembership]
(
@InAtomID VARCHAR(50)
)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @SCACLists AS VARCHAR(255)
DECLARE @SCACListsTemp AS VARCHAR(50)

DECLARE db_cursor CURSOR FAST_FORWARD FOR
SELECT
g.Name
FROM dbo.UserGroupMembership u
JOIN dbo.Groups g ON u.GroupAtomId = g.GroupsAtomId
WHERE useratomid = @InAtomID

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @SCACListsTemp
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SCACLists = @SCACListsTemp
FETCH NEXT FROM db_cursor INTO @SCACListsTemp
END
CLOSE db_cursor
DEALLOCATE db_cursor

RETURN @SCACLists

END


This part:
SET @SCACLists = @SCACListsTemp

I want to make it as
SET @SCACLists = @SCACLists + @SCACListsTemp

but when I do that, @SCACLists will not yield any value unlike the first wherein only the last value of @SCACListsTemp is saved to @SCACLists.

What seems to be the problem here? Does the @SCACLists losses its value inside the loop?


Thanks

---

CREATE FUNCTION [dbo].[fnGetSCACMembership]
(
@InAtomID VARCHAR(50)
)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @SCACLists AS VARCHAR(255)

SELECT @SCACLists=COALESCE(@SCACLists + ',','') + 
g.Name
FROM dbo.UserGroupMembership u
JOIN dbo.Groups g ON u.GroupAtomId = g.GroupsAtomId
WHERE useratomid = @InAtomID

RETURN @SCACLists

END

quote:

---

Originally posted by visakh16

you dont need cursor at all. what you need is just this

CREATE FUNCTION [dbo].[fnGetSCACMembership]
(
@InAtomID VARCHAR(50)
)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @SCACLists AS VARCHAR(255)

SELECT @SCACLists=COALESCE(@SCACLists + ',','') + 
g.Name
FROM dbo.UserGroupMembership u
JOIN dbo.Groups g ON u.GroupAtomId = g.GroupsAtomId
WHERE useratomid = @InAtomID

RETURN @SCACLists

END

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

---

quote:

---

Originally posted by visakh16

it didnt work as you didnt initialise the variable @SCACLists . it will be null by default so you need to do

SET @SCACLists = COALESCE(@SCACLists,'') + @SCACListsTemp
instead of just

SET @SCACLists = @SCACLists + @SCACListsTemp



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

---