Return an xml string from a procedure and assign it to a variable

Transact-SQL
1

I have some code that creates a stored proc , what i want it to do is return a string that is xml so I can store it in a variable but it doesn't seem work.

USE [master]
GO
/****** Object: StoredProcedure [dbo].[Get_ADGroups_ForUser] Script Date: 9/13/2016 3:38:50 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Get_ADGroups_ForUser]
(
@Username NVARCHAR(256),
@GroupNames NVARCHAR(1024) OUTPUT
)
AS
BEGIN

DECLARE @Query NVARCHAR(1024), @Path NVARCHAR(1024), @Groups NVARCHAR(1024)

SET @Query = '
    SELECT @Path = distinguishedName
    FROM OPENQUERY(ADSI, ''
        SELECT distinguishedName 
        FROM ''''LDAP://DC=here,DC=com''''
        WHERE 
            objectClass = ''''user'''' AND
            sAMAccountName = ''''' + @Username + '''''
    '')
'
EXEC SP_EXECUTESQL @Query, N'@Path NVARCHAR(1024) OUTPUT', @Path = @Path OUTPUT 

SET @Query = '
    SELECT cn
    FROM OPENQUERY (ADSI, ''<LDAP://DC=here,DC=com>;(&(objectClass=group)(member:1.2.840.113556.1.4.1941:=' + @Path +'));cn;subtree'')   where cn LIKE ''SAS%'' FOR XML PATH('''')'

EXEC SP_EXECUTESQL @Query, N'@GroupNames NVARCHAR(1024) OUTPUT', @GroupNames =  @GroupNames OUTPUT

Return;
END

and here is how I am calling it

declare @GroupNames nvarchar(1024)

EXEC Get_ADGroups_ForUser 'robm' ,@GroupNames output

print '1'
print @GroupNames
print '2'

but all I get back is a results table

on the message screen I get

(4 row(s) affected)
1

2

any idea on why that @GroupNames isnt getting populated?

2

In your second query, you are not assigning the result to the @GroupNames variable. You have to do that just like you did with @Path variable in the first query. You cannot directly assign the result of a XML accumulator to a variable, so you might have to do something like this:

SET @Query = 'SELECT @GroupNames = (
    SELECT cn
    FROM OPENQUERY (ADSI, ''<LDAP://DC=here,DC=com>;(&(objectClass=group)(member:1.2.840.113556.1.4.1941:=' + @Path +'));cn;subtree'')   where cn LIKE ''SAS%'' FOR XML PATH('''')'
    + ')';
1 Like
3

thanks James that worked!