Is it possible to join the result set of a stored proc with a table?

Question

Is it possible to join the result set of a stored proc with a table?

sandra pastrana

SSC Eights!

Points: 979
More actions
November 30, 2007 at 12:13 pm

#180871

Is it possible to join the result set of a stored proc with a table, without inserting the result set on a temp table?
here is my stored procedure:
create proc P_DATABASEUSERS
as
IF OBJECT_ID('tempdb..#DatabaseUsers') is not null
drop table #DatabaseUsers
create table #DatabaseUsers(DatabaseNamesysname not null,DatabaseUserNamesysname not null,LoginNamesysname null)
-- sys.database_principals.type is S = SQL user, U = Windows user, G = Windows group
exec master.dbo.sp_MSforeachdb @replacechar = N'?',
@command1 = 'insert into #DatabaseUsers(DatabaseName,DatabaseUserName,LoginName)select''?'',
DatabaseUsers.name as DatabaseUserName,suser_sname( DatabaseUsers.SID) as LoginName
from[?].sys.database_principalsas DatabaseUsers
whereDatabaseUsers.type in (''S'',''G'',''U'')'
select * from #DatabaseUsers
GO
I WOULD LIKE TO JOIN THE RESULT SET OF THIS STORED PROC TO SYS.SYSLOGINS

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Not directly, as SL Server does not allow you to use a stored procedure like a table. You will need to load the output of the stored procedure into a temporary table or table variable, and then join that to the sys.logins view.

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Lynn Pettis (11/30/2007)
Not directly, as SL Server does not allow you to use a stored procedure like a table. You will need to load the output of the stored procedure into a temporary table or table variable, and then join that to the sys.logins view.

Along those lines look at EXECUTE..INTO in BOL.

noeld SSC Guru Points: 96590 More actions · Answer 3

Or if you are really hard pressed take a look at "openrowset"/"opendatasource"

* Noel

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Ex. from your other post I just gave

EXEC INTO #DatabaseUsers sp_MSforeachdb

Antares686 SSC Guru Points: 125444 More actions · Answer 5

Sorry I am working to much today, here is what the example should read

...

INSERT INTO #DatabaseUsers EXEC sp_MSforeachdb...

noeld SSC Guru Points: 96590 More actions · Answer 6

Antares686 (11/30/2007)
Sorry I am working to much today, here is what the example should read
...

That is when I would go for coffee 😀

* Noel

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

Heh... at that point... I'd be thinking "BEER"! 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Antares686 SSC Guru Points: 125444 More actions · Answer 8

Jeff Moden (11/30/2007)
Heh... at that point... I'd be thinking "BEER"! 😀

Smirnof Ice for me or maybe it's big brother Smirnof Black, not a coffee or beer drinker.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Antares686 (11/30/2007)
Jeff Moden (11/30/2007)
Heh... at that point... I'd be thinking "BEER"! 😀
Smirnof Ice for me or maybe it's big brother Smirnof Black, not a coffee or beer drinker.

I'm with you regarding coffee and beer, I don't drink either.

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 10

Jeff Moden (11/30/2007)
Heh... at that point... I'd be thinking "BEER"! 😀

It's about time you starting talking my language. Well, second language. :w00t:

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 11

Lynn Pettis (11/30/2007)
Antares686 (11/30/2007)
Jeff Moden (11/30/2007)
Heh... at that point... I'd be thinking "BEER"! 😀
Smirnof Ice for me or maybe it's big brother Smirnof Black, not a coffee or beer drinker.
I'm with you regarding coffee and beer, I don't drink either.

Any of the above would work, especially if I can use it as an excuse to duck out of here.....:cool:

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Carl Federl One Orange Chip Points: 25384 More actions · Answer 12

I have a number of recommendation.

1. Do not attempt to join the result set of a stored procedure to another table. Instead combine the SQL from the stored procedure and the additional needed statements into a single SQL Statement and create a new procedure if needed.

2. SYS.SYSLOGINS is a legacy view and sys.server_principles is prefered.

3. Not all database users (sys.database_principals) will have corresponding row in sys.server_principles based on SID. That is, database users do not need to have any correspondence to a login. This is true for SQL Server 7, 2000, 2005 and 2008. Once case is when the logins are an Active Directory group but the database user is an individual Active Directory account. The weirdest case is database ownership;

create database carlfederl

go

-- change db owner to an AD account that does not have access.

exec carlfederl.dbo.sp_changedbowner @loginame = 'domainame',@map='true'

go

exec sp_helpdb

go

select *

fromsys.sysdatabases

wherenot exists

(select 1

fromsys.server_principals

wheresys.server_principals.sid = sys.sysdatabases.sid

)

go

drop database carlfederl

In your case, just modify the stored procedure SQL such as:

select columns

from#DatabaseUsers

left outer join sys.server_principals

on sys.server_principals.SID = #DatabaseUsers.SID

SQL = Scarcely Qualifies as a Language