> The problems are two: 1) How do I make a call to GetUserInfoFromRole
> (which is within the Membership DB)

> information to the table variable.

<ffrugone@gmail.com> wrote in message
news:1153808737.652269.136500@m79g2000cwm.googlegroups.com...
> OK, so here's the deal:
>
> Using .NET 2.0 Membership DB for basic username, password, and other
> login data. However, due to it's limitations and the headache that is
> Profiles, I have the rest of my user information in another database on
> the server, (that I call MyBigDB).
>
> I wrote an SP within the Membership DB that gathers UserNames and
> UserIDs for a given Role, (that I call GetUserInfoFromRole). I'm trying
> to write an SP on MyBigDB that has a nested call to
> GetUserInfoFromRole, and that will assign returned data to a table
> variable, (if I can, since I read that table variables can't be used to
> hold returned information from a nested SP, and that I'd have to use a
> temporary table) so that I can join it with other tables from MyBigDB
> before returning that data to the calling client.
>
> The problems are two: 1) How do I make a call to GetUserInfoFromRole
> (which is within the Membership DB) and then 2) Assign the returned
> information to the table variable.
>
> Here is SOME OF THE CODE from GetUserInfoFromRole:
> ******************************************************************************
> ALTER PROCEDURE dbo.aspnet_GetUserInfoFromRole
> @ApplicationName nvarchar(256),
> @RoleName nvarchar(256),
>
> AS
>
> --Code omitted for brevity
>
> SELECT u.UserName, u.UserID
> FROM dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur
> WHERE u.UserId = ur.UserId AND @RoleId = ur.RoleId AND
> u.ApplicationId = @ApplicationId
> ORDER BY u.UserName
> RETURN(0)
> END
>
> ******************************************************************************
>
> Here is what I THINK I need to have:
> ******************************************************************************
> ALTER PROCEDURE dbo.MyBigDB_DoWorkOnReturnedData
> @ApplicationName nvarchar(256)
> @RoleName nvarchar(256)
> AS
>
> BEGIN
> DECLARE @infoHolder table (UserName nvarchar(256), UserID
> uniqueidentifier)
>
> USE ASPNETDB
> INSERT INTO @infoHolder
> EXEC dbo.aspnet_GetUserInfoFromRole (@ApplicationName,
> @RoleName)
>
> --Now: somehow stop using ASPNETDB and use MyBigDB. Maybe with:
> USE MyBigDB
>
> --Work with the @infoHolder data
>
> END
> ******************************************************************************
>
> Any evaluation or suggestions regarding the above would be greatly
> appreciated. My hat is off to those that seek to help through this
> forum.
>
> ffrugone
>