Joining one database table with other database table in stroed procedures.

Hi All,
I need to perform a join on table1 of database1 with table2 or database2, in a stored procedure and return to my web application.. For this I'm providing execute permission on stored procedure, in database1 and "SELECT" permission on database2 table 2, to my database webuser.
Is there any way ( Another stored procedure in database2), i can get join on two tables without SELECT permission right on table2 ( or table1).
Purely using stored procedures. If so how?Normally you only need to grant execute permission on a stored procedure not select permission on the underlying tables.

Past that I would setup a sp to on database1 to return the data and make sure the webuser has an a security path from database1 to database2. How do yo uhave security setup for this user on databse1 and database2?|||Hi Paul,
Thanks, What is meant by security path? I didnot get ur point.
User have only public permission on both databases. Is it not enough? I do have execute permission on sp1 (Stored Procedure) in database1.
but in sp1 I have a select statement which is a join on database2 table-'table2'. When I tried to execute sp1 from web application only with execute permission on sp1-
I got an error as Webuser does not have 'SELECT' permission on table2 of database2. So I provided it and sp1 worked fine. But I don't want to use a SELECT permission- What is alternate, for the join.
sp1 has simple
SELECT *
FROM database1.dbo.table1 t1
INNER JOIN database2.dbo.table2 t2
ON (t1.col1 = t2.col1)

Originally posted by Paul Young
Normally you only need to grant execute permission on a stored procedure not select permission on the underlying tables.

Past that I would setup a sp to on database1 to return the data and make sure the webuser has an a security path from database1 to database2. How do yo uhave security setup for this user on databse1 and database2?|||by security path I was refering to how a user gets authenticated on server2 when making a connection from server1.

You might try using OPENQUERY to call a stored procedure on server2 and use the results to join to a table on server1. I haven't had the need for this in the past so I am working on theory here. Check BOL for usage on OPENQUERY, they have some good examples.|||When calling a stored proc from database 1, you need execute privs on the proc. You do not need to give the user privs on the table itself as long as it is in database 1, too. If database 2 is owned by the same user that is the owner of database 1, you do not need explicit permissions on the object in table 2 accessed by the proc. However, if the database owners are different for the two databases, the user calling the proc must have explicit permissions in the second database.

If need be, you can change the database owner by:

EXEC sp_changedbowner 'username'

Execute this in the database you want to change.|||Well Both databases are on same server. Is OPENQUERY solve in this case too?|||Originally posted by soumyag
Well Both databases are on same server.

It doesn't matter what server they are on, the owner of the databases is what matters. You can have multiple databases on the same server with different owners. In query analyzer, run:

sp_helpdb

It will list the owner for each of the databases. If the owner is different, run sp_changedbowner to set them the same.|||Hi,
Both are on same server, but owned by different users. And I don't have any right to change the dbowners But how it will help in writing a join on tables. What is other alternative way to solve this problem.
Thanks.

Originally posted by bglass

It doesn't matter what server they are on, the owner of the databases is what matters. You can have multiple databases on the same server with different owners. In query analyzer, run:

sp_helpdb

It will list the owner for each of the databases. If the owner is different, run sp_changedbowner to set them the same.

Joining datasets returned from stored procedures.

Hello,
I am restricted to using stored procedures for data access. I would like to
be able to create a "Text" Dataset named C which is a join of two "Stored
Procedure" data sets (sp1, and sp2). Any idea how I can do this. Thanks.Tyr this:
SELECT * FROM OPENQUERY(servername, 'sp_1')
union
SELECT * FROM OPENQUERY(servername, 'sp_2')
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
news:%23%23nvqx4YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I am restricted to using stored procedures for data access. I would like
to
> be able to create a "Text" Dataset named C which is a join of two "Stored
> Procedure" data sets (sp1, and sp2). Any idea how I can do this. Thanks.
>

Join stored procedures?

Hi all,

I have a challenge (not a problem). Simplifing it as far as i could, i
have this situation:

Table Persons
Id (PK)
Name

Table Scores
FK_Person (PK)
Period (PK)
Value

Now, i want to make a stored procedure that returns the scores for all
people in a certain period. So i have:

CREATE PROCEDURE [dbo].[ScorePerson]
(@.Period Int)
AS
SELECT dbo.People.Name, dbo.Scores.[Value]
FROM dbo.People LEFT OUTER JOIN
dbo.Scores ON dbo.People.Id =
dbo.Scores.FK_person
WHERE dbo.Scores.Period = @.Period
GO

BUT: if a person has no score for a certain period, he will not show
up in the result set at all, but i do want him to. So what i need to
do is first get the subset for a period from the table Scores and THEN
Left join that to the persons, so i always get all persons and only a
value for the the score if there is one.

How can i do this? I could store the results in a temporary table but
that just feels 'unpure', wrong. Is there any way i can get this in a
single pass? Can i for example join two stored procedures with
parameters together in another SP that passes these params on to them?

TIA,

Gert-JanIf you have no Periods table, you can get the list of periods based on your
Scores table. Of course, this technique will only return periods with at
least one row in the Scores table so you'll need a Periods table if you need
to include periods with no scores. Below is an example:

SELECT
dbo.People.Name,
dbo.Scores.[Value]
FROM dbo.People
LEFT JOIN dbo.Scores ON
dbo.People.Id = dbo.Scores.FK_person AND
dbo.Scores.Period = @.Period
CROSS JOIN
(SELECT DISTINCT Period
FROM Scores) AS Periods
WHERE Periods.Period = @.Period

--
Hope this helps.

Dan Guzman
SQL Server MVP

"G.J. v.d. Kamp" <gjvdkamp@.hotmail.com> wrote in message
news:d49d68a1.0411250718.59c0b023@.posting.google.c om...
> Hi all,
> I have a challenge (not a problem). Simplifing it as far as i could, i
> have this situation:
> Table Persons
> Id (PK)
> Name
> Table Scores
> FK_Person (PK)
> Period (PK)
> Value
> Now, i want to make a stored procedure that returns the scores for all
> people in a certain period. So i have:
> CREATE PROCEDURE [dbo].[ScorePerson]
> (@.Period Int)
> AS
> SELECT dbo.People.Name, dbo.Scores.[Value]
> FROM dbo.People LEFT OUTER JOIN
> dbo.Scores ON dbo.People.Id =
> dbo.Scores.FK_person
> WHERE dbo.Scores.Period = @.Period
> GO
> BUT: if a person has no score for a certain period, he will not show
> up in the result set at all, but i do want him to. So what i need to
> do is first get the subset for a period from the table Scores and THEN
> Left join that to the persons, so i always get all persons and only a
> value for the the score if there is one.
> How can i do this? I could store the results in a temporary table but
> that just feels 'unpure', wrong. Is there any way i can get this in a
> single pass? Can i for example join two stored procedures with
> parameters together in another SP that passes these params on to them?
> TIA,
> Gert-Jan|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

You also need to read a basic book on data modeling and the ISO-11179
naming conventions. How many different names did you post for the same
data element? Why are tables that share a data element modeled as if
they had no DRI relationships in your personal pseudo-code?

CREATE TABLE Persons
(player_id INTEGER NOT NULL PRIMARY KEY,
name CHAR(35) NOT NULL);

CREATE TABLE Games
(player_id INTEGER NOT NULL
REFERENCES Persons (player_id),
period_nbr INTEGER NOT NULL,
score INTEGER NOT NULL CHECK (score >= 0));

>> I want to make a stored procedure that returns the scores for all
people in a certain period. <<

Why do you still think in procedural terms in a non-procedural
language? Why not a VIEW?

CREATE VIEW TotalScores (player_name, period_nbr, score_tot)
AS
SELECT P.player_name, G.period_nbr, SUM(G.score_tot)
FROM Persons AS P
LEFT OUTER JOIN
Games AS G
ON P.player_id = G.player_id
GROUP BY player_id, period_nbr;

Then you can use this query

SELECT player_name, @.my_period_nbr, score_tot
FROM TotalScores
WHERE @.my_period_nbr = period_nbr;

A zero means he played and did not score; a NULL means he did not play
at all.|||On 25 Nov 2004 10:39:49 -0800, --CELKO-- wrote:

(snip)
>CREATE VIEW TotalScores (player_name, period_nbr, score_tot)
>AS
>SELECT P.player_name, G.period_nbr, SUM(G.score_tot)
> FROM Persons AS P
> LEFT OUTER JOIN
> Games AS G
> ON P.player_id = G.player_id
> GROUP BY player_id, period_nbr;

Hi Joe,

Maybe you should have tested this before posting!

Server: Msg 170, Level 15, State 1, Procedure TotalScores, Line 8
Line 8: Incorrect syntax near ';'.

(Okay, this is buggy behaviour of SQL Server - but this IS a SQL Server
group, after all!)

Remove the semicolon; retry:

Server: Msg 207, Level 16, State 3, Procedure TotalScores, Line 3
Invalid column name 'player_name'.
Server: Msg 207, Level 16, State 1, Procedure TotalScores, Line 3
Invalid column name 'score_tot'.
Server: Msg 209, Level 16, State 1, Procedure TotalScores, Line 3
Ambiguous column name 'player_id'.

Change player_name to name and score_tot to score in the SELECT and add P.
in front of player_id in the GROUP BY; retry:

Server: Msg 8120, Level 16, State 1, Procedure TotalScores, Line 3
Column 'P.name' is invalid in the select list because it is not contained
in either an aggregate function or the GROUP BY clause.

Okay, add P.name to the group by (or enclose it in an aggregate function),
retry and finally the view is made (whew!).

>Then you can use this query
>SELECT player_name, @.my_period_nbr, score_tot
> FROM TotalScores
> WHERE @.my_period_nbr = period_nbr;
>A zero means he played and did not score; a NULL means he did not play
>at all.

Let's enter some data to test it. Two persons (Joe and Hugo). Both have a
score in period 1, Joe has a score in period 2 and Hugo in period 3.

insert Persons values(1,'Joe')
insert Persons values(2,'Hugo')
insert Games values (1, 1, 1)
insert Games values (2, 1, 2)
insert Games values (1, 2, 3)
insert Games values (2, 3, 0)
GO
declare @.my_period_nbr int
set @.my_period_nbr = 1
SELECT player_name, @.my_period_nbr, score_tot
FROM TotalScores
WHERE @.my_period_nbr = period_nbr;
set @.my_period_nbr = 2
SELECT player_name, @.my_period_nbr, score_tot
FROM TotalScores
WHERE @.my_period_nbr = period_nbr;
set @.my_period_nbr = 3
SELECT player_name, @.my_period_nbr, score_tot
FROM TotalScores
WHERE @.my_period_nbr = period_nbr;

Does this return the data that the poster asked for? Does this return Hugo
in period 2 and Joe in period 3, even though they have no score in that
period?

Unfortunately - no.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 25 Nov 2004 07:18:55 -0800, G.J. v.d. Kamp wrote:

(snip)
>BUT: if a person has no score for a certain period, he will not show
>up in the result set at all, but i do want him to. So what i need to
>do is first get the subset for a period from the table Scores and THEN
>Left join that to the persons, so i always get all persons and only a
>value for the the score if there is one.

Hi Gert-Jan,

This is actually lots simpler than you think!

SELECT dbo.People.Name, dbo.Scores.[Value]
FROM dbo.People LEFT OUTER JOIN
dbo.Scores ON dbo.People.Id =
dbo.Scores.FK_person
AND dbo.Scores.Period = @.Period

(The only change is to move the filter condition for period to the join
condition!!)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi all,

Thanks everyone for your time.

Dan, works like a charm, thanks! It obviously does help to have a
thorough foundation in math.

Celko, i only described the problem in a generic way, i thought it
would be a common problem a person more experienced than me would
recognize. (and they did). Also, a view doesn't take parameters as far
as i know. THe point about SQL not being a procedural language is
usually a good one, personally i don't think very highly of people who
break out cursors for the simplest of inserts. But i don't think it's
just in this case. Thanks anyway.

But of course first prize goes to Hugo for the lean of going about it.
I like lean, and now i like Hugo (In a friendly way, don't worry!)
Thanks!

Regards GJ

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<h5qcq09cmd963c7vutuq1v2ru9gqdd7rld@.4ax.com>...
> On 25 Nov 2004 07:18:55 -0800, G.J. v.d. Kamp wrote:
> (snip)
> >BUT: if a person has no score for a certain period, he will not show
> >up in the result set at all, but i do want him to. So what i need to
> >do is first get the subset for a period from the table Scores and THEN
> >Left join that to the persons, so i always get all persons and only a
> >value for the the score if there is one.
> Hi Gert-Jan,
> This is actually lots simpler than you think!
> SELECT dbo.People.Name, dbo.Scores.[Value]
> FROM dbo.People LEFT OUTER JOIN
> dbo.Scores ON dbo.People.Id =
> dbo.Scores.FK_person
> AND dbo.Scores.Period = @.Period
> (The only change is to move the filter condition for period to the join
> condition!!)
> Best, Hugo|||Celko,

Your solution will probably work as well, but i just like to keap my
asp code as neat as possible, so i move all the logic to the
SQL-server as much as i can. Also, in my real world problem, it would
theoretically be possible to have a zero score as a value.

But thanks anyway.

Regards GJ

jcelko212@.earthlink.net (--CELKO--) wrote in message news:<18c7b3c2.0411251039.116b316d@.posting.google.com>...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications.
> You also need to read a basic book on data modeling and the ISO-11179
> naming conventions. How many different names did you post for the same
> data element? Why are tables that share a data element modeled as if
> they had no DRI relationships in your personal pseudo-code?
> CREATE TABLE Persons
> (player_id INTEGER NOT NULL PRIMARY KEY,
> name CHAR(35) NOT NULL);
> CREATE TABLE Games
> (player_id INTEGER NOT NULL
> REFERENCES Persons (player_id),
> period_nbr INTEGER NOT NULL,
> score INTEGER NOT NULL CHECK (score >= 0));
> >> I want to make a stored procedure that returns the scores for all
> people in a certain period. <<
> Why do you still think in procedural terms in a non-procedural
> language? Why not a VIEW?
> CREATE VIEW TotalScores (player_name, period_nbr, score_tot)
> AS
> SELECT P.player_name, G.period_nbr, SUM(G.score_tot)
> FROM Persons AS P
> LEFT OUTER JOIN
> Games AS G
> ON P.player_id = G.player_id
> GROUP BY player_id, period_nbr;
> Then you can use this query
> SELECT player_name, @.my_period_nbr, score_tot
> FROM TotalScores
> WHERE @.my_period_nbr = period_nbr;
> A zero means he played and did not score; a NULL means he did not play
> at all.|||"G.J. v.d. Kamp" <gjvdkamp@.hotmail.com> wrote in message news:d49d68a1.0411260025.681e9a65@.posting.google.c om...
> Also, a view doesn't take parameters as far
> as i know.

Views don't take parameters in the same way as procedures and functions, but you can still use your parameters...

Select *
from myView
where myView.col1 = @.myParameter ;

The view is created without the parameter @.myParameter, but it's easy to write queries on the view that use them.

--
Paul Horan
Sr. Architect
VCI Springfield, Mass
www.vcisolutions.com