calling one stored procedure from another

calling one stored procedure from another

Post by Slot » Wed, 19 Feb 2003 20:07:27



Hi

I'm sure this question has been answered many times before, but i've had no
luck finding an answer yet!

I have one stored procedure (sp_GetUserRolesList) that returns a string
(RoleList) containing the groups that a user belongs to e.g. 'Clients,
Admins, Testers'. This procedure has one param - UserID.
I have another procedure, GetAllUserDetails, that i want to return columns:
Users.UserName, Users.UserCompany etc. (from Users table) and RoleList (from
sp_GetUserRolesList).

The data i want would be something like:

UserName | UserCompany | RoleList
Fred | ACME | Clients, Testers
Bill | Connex | Clients
Jon | ACME | Admins, Testers

How do i go about doing this? I've looked all over the place, am i supposed
to use a temp table?

Seems like such a simple problem!

thanks in advance

Rik

 
 
 

calling one stored procedure from another

Post by Falik She » Wed, 19 Feb 2003 20:14:41


from the 2nd SP, you need to create the temp table and get the results from
the 1 SP into that then combine both and send back to client application
HTH
Falik


Quote:> Hi

> I'm sure this question has been answered many times before, but i've had
no
> luck finding an answer yet!

> I have one stored procedure (sp_GetUserRolesList) that returns a string
> (RoleList) containing the groups that a user belongs to e.g. 'Clients,
> Admins, Testers'. This procedure has one param - UserID.
> I have another procedure, GetAllUserDetails, that i want to return
columns:
> Users.UserName, Users.UserCompany etc. (from Users table) and RoleList
(from
> sp_GetUserRolesList).

> The data i want would be something like:

> UserName | UserCompany | RoleList
> Fred | ACME | Clients, Testers
> Bill | Connex | Clients
> Jon | ACME | Admins, Testers

> How do i go about doing this? I've looked all over the place, am i
supposed
> to use a temp table?

> Seems like such a simple problem!

> thanks in advance

> Rik


 
 
 

calling one stored procedure from another

Post by Slot » Wed, 19 Feb 2003 20:22:25


Falik

thanks for the quick reply!

I'm pretty much an absolute beginner on this, so need a bit more help!

What would the syntax be for calling the first SP from the second, for each
User in the Users Table?

obviously, Select UserID from Users, but then where would i put the "exec
sp_GetUserRolesList Users.UserID" bit ?

thanks

Rik


> from the 2nd SP, you need to create the temp table and get the results
from
> the 1 SP into that then combine both and send back to client application
> HTH
> Falik



> > Hi

> > I'm sure this question has been answered many times before, but i've had
> no
> > luck finding an answer yet!

> > I have one stored procedure (sp_GetUserRolesList) that returns a string
> > (RoleList) containing the groups that a user belongs to e.g. 'Clients,
> > Admins, Testers'. This procedure has one param - UserID.
> > I have another procedure, GetAllUserDetails, that i want to return
> columns:
> > Users.UserName, Users.UserCompany etc. (from Users table) and RoleList
> (from
> > sp_GetUserRolesList).

> > The data i want would be something like:

> > UserName | UserCompany | RoleList
> > Fred | ACME | Clients, Testers
> > Bill | Connex | Clients
> > Jon | ACME | Admins, Testers

> > How do i go about doing this? I've looked all over the place, am i
> supposed
> > to use a temp table?

> > Seems like such a simple problem!

> > thanks in advance

> > Rik

 
 
 

calling one stored procedure from another

Post by Falik She » Wed, 19 Feb 2003 20:37:50


in 2nd SP
...
...
create table #temp1 (all required fields typically which your SP1 is going
to return)
then
insert into #temp1 (field list)
EXEC SP1

This will insert data into #temp1 table which you can use, the way like.

Hope this make some sens, in case, post the DDL of tables, SP1 and SP2
HTH
Falik


> Falik

> thanks for the quick reply!

> I'm pretty much an absolute beginner on this, so need a bit more help!

> What would the syntax be for calling the first SP from the second, for
each
> User in the Users Table?

> obviously, Select UserID from Users, but then where would i put the "exec
> sp_GetUserRolesList Users.UserID" bit ?

> thanks

> Rik



> > from the 2nd SP, you need to create the temp table and get the results
> from
> > the 1 SP into that then combine both and send back to client application
> > HTH
> > Falik



> > > Hi

> > > I'm sure this question has been answered many times before, but i've
had
> > no
> > > luck finding an answer yet!

> > > I have one stored procedure (sp_GetUserRolesList) that returns a
string
> > > (RoleList) containing the groups that a user belongs to e.g. 'Clients,
> > > Admins, Testers'. This procedure has one param - UserID.
> > > I have another procedure, GetAllUserDetails, that i want to return
> > columns:
> > > Users.UserName, Users.UserCompany etc. (from Users table) and RoleList
> > (from
> > > sp_GetUserRolesList).

> > > The data i want would be something like:

> > > UserName | UserCompany | RoleList
> > > Fred | ACME | Clients, Testers
> > > Bill | Connex | Clients
> > > Jon | ACME | Admins, Testers

> > > How do i go about doing this? I've looked all over the place, am i
> > supposed
> > > to use a temp table?

> > > Seems like such a simple problem!

> > > thanks in advance

> > > Rik

 
 
 

calling one stored procedure from another

Post by Suppor » Wed, 19 Feb 2003 21:35:36


Rik:
It looks to me like a simple join would work
Select Users.UserName, Users.UserCompany, RoleList.Role
FROM
Users
Join Role on User.UserID=RileList.UserID

Sorry but I  dont see the ned to use a temptable.  Alos, take a look at
udf's (user defined function)
Terry

Quote:> Hi

> I'm sure this question has been answered many times before, but i've had
no
> luck finding an answer yet!

> I have one stored procedure (sp_GetUserRolesList) that returns a string
> (RoleList) containing the groups that a user belongs to e.g. 'Clients,
> Admins, Testers'. This procedure has one param - UserID.
> I have another procedure, GetAllUserDetails, that i want to return
columns:
> Users.UserName, Users.UserCompany etc. (from Users table) and RoleList
(from
> sp_GetUserRolesList).

> The data i want would be something like:

> UserName | UserCompany | RoleList
> Fred | ACME | Clients, Testers
> Bill | Connex | Clients
> Jon | ACME | Admins, Testers

> How do i go about doing this? I've looked all over the place, am i
supposed
> to use a temp table?

> Seems like such a simple problem!

> thanks in advance

> Rik

 
 
 

calling one stored procedure from another

Post by Slot » Wed, 19 Feb 2003 21:40:22


Terry

RoleList is the return value from the 1st stored proc. (GetUserRolesList),
not a column in a table

Rik


> Rik:
> It looks to me like a simple join would work
> Select Users.UserName, Users.UserCompany, RoleList.Role
> FROM
> Users
> Join Role on User.UserID=RileList.UserID

> Sorry but I  dont see the ned to use a temptable.  Alos, take a look at
> udf's (user defined function)
> Terry


> > Hi

> > I'm sure this question has been answered many times before, but i've had
> no
> > luck finding an answer yet!

> > I have one stored procedure (sp_GetUserRolesList) that returns a string
> > (RoleList) containing the groups that a user belongs to e.g. 'Clients,
> > Admins, Testers'. This procedure has one param - UserID.
> > I have another procedure, GetAllUserDetails, that i want to return
> columns:
> > Users.UserName, Users.UserCompany etc. (from Users table) and RoleList
> (from
> > sp_GetUserRolesList).

> > The data i want would be something like:

> > UserName | UserCompany | RoleList
> > Fred | ACME | Clients, Testers
> > Bill | Connex | Clients
> > Jon | ACME | Admins, Testers

> > How do i go about doing this? I've looked all over the place, am i
> supposed
> > to use a temp table?

> > Seems like such a simple problem!

> > thanks in advance

> > Rik

 
 
 

calling one stored procedure from another

Post by Slot » Sat, 22 Feb 2003 18:00:27


is OK - i solved this problem usinga user defined function. easy!


> Terry

> RoleList is the return value from the 1st stored proc. (GetUserRolesList),
> not a column in a table

> Rik



> > Rik:
> > It looks to me like a simple join would work
> > Select Users.UserName, Users.UserCompany, RoleList.Role
> > FROM
> > Users
> > Join Role on User.UserID=RileList.UserID

> > Sorry but I  dont see the ned to use a temptable.  Alos, take a look at
> > udf's (user defined function)
> > Terry


> > > Hi

> > > I'm sure this question has been answered many times before, but i've
had
> > no
> > > luck finding an answer yet!

> > > I have one stored procedure (sp_GetUserRolesList) that returns a
string
> > > (RoleList) containing the groups that a user belongs to e.g. 'Clients,
> > > Admins, Testers'. This procedure has one param - UserID.
> > > I have another procedure, GetAllUserDetails, that i want to return
> > columns:
> > > Users.UserName, Users.UserCompany etc. (from Users table) and RoleList
> > (from
> > > sp_GetUserRolesList).

> > > The data i want would be something like:

> > > UserName | UserCompany | RoleList
> > > Fred | ACME | Clients, Testers
> > > Bill | Connex | Clients
> > > Jon | ACME | Admins, Testers

> > > How do i go about doing this? I've looked all over the place, am i
> > supposed
> > > to use a temp table?

> > > Seems like such a simple problem!

> > > thanks in advance

> > > Rik

 
 
 

1. JDBC : call one Store Procedure returning one table

Hello

Do you know how I can read the results of one store Procedure, which
returns the contain of one temporary table ?
I would like to call one store procedure and read the results of this
store procedure, each line of the table.

I know how to call one store procedure, how to read one return
parameter.
But my store procedure return more parameters : more table columns and
tuples, how can read and the store such results ?

I use JDBC 1.2.2 and Sql Server 6.5 and BEA jDriver/MSSQLServer4 Kit
5.1.0 For MSSQLServer Version 6 and 6.5.

2. How does a base relation differ from a table?

3. Calling one stored procedure from another

4. Compaq/office/Progress 8.3b hangs

5. Informix Server 7.31 no response?!

6. calling one stored procedure from another

7. Measuring Performance/Tuning

8. syntax errors calling one stored procedure from another...

9. Calling Stored Procedure that calls Stored Procedure in ASP

10. Calling a Java Stored Procedure from another Java Stored Stored Procedure

11. Call stored procedure from an other one

12. One way to call DB2 stored procedures through VB 6 with ADO/RDO