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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply