September 20, 2004 at 8:17 am
In SQL Server 2000, is it possible to use the results returned by a stored procedure directly, or does one have to use a temporary table?
I want to get a list of users from a Windows 2000 Group. The following code works:
SET NOCOUNT ON
CREATE TABLE #Users (account_name varchar(128)
, type char(8)
, privilege char(9)
, mapped_login_name varchar(128)
, permission_path varchar(128))
INSERT INTO #Users EXEC master..xp_logininfo 'NTDomain\NTGroup', 'members'
SET NOCOUNT OFF
SELECT account_name
FROM #Users
ORDER BY account_name
DROP TABLE #Users
I was just wondering if there is a way of doing the above without using a temporary table, i.e.:
SELECT account_name
FROM (EXEC master..xp_logininfo 'NTDomain\NTGroup', 'members')
ORDER BY account_name
Thanks,
Barry
September 20, 2004 at 8:50 am
Hi, I don't know if this help you... but instead of T-SQL you can build a DTS with the sp call as the input of the Data Transfer and 'account_name' as the outpur.
Regards
Jorge
September 21, 2004 at 3:43 am
Hi!
With SP you have to use temporary table. With XP also.
SP you can rewrite into UDF, XP - no way.
September 21, 2004 at 4:12 am
Thanks Yukas, thought that was probably the case, just wanted to be sure I wasn't missing a trick!
Regards,
Barry
September 21, 2004 at 10:10 am
Like Yukas said, you could rewrite the SP as a UDF.
Another alternative would be to keep your SP, but revise it to use a table variable instead of a temp table.
If your values for the xp_logininfo parameters will change, then I'd rewrite the SP as a UDF.
September 22, 2004 at 2:51 am
George.
With XP you have to use temp table, you cannot use table variable.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy