Calling a procedure from another procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calling a procedure from another procedure

Author

Topic

sgi
Starting Member

1 Post

Posted - 2005-11-02 : 09:05:35

Hello Members,

I have been unable to come up with a solution to the problem mentioned below. Hope someone could point me in the right direction.
If this is not the right forum, please point me to the right one.

Have a database (Database A) and a stored procedure (spr_ProcedureA) that returns about 40 fields. Access to the tables in Database A are strictly through this procedure.

I have another database(Database B) residing on another server from where I will have a procedure call/exec the sp_ProcedureA in Database A from Database B and insert the resultset in a staging table in Database B. Need only 7 fields of the 40 returned.

Question: How will I exec and insert in the staging table selective columns when no table names(from Database B) are not to be used.

For example :

Insert Into tb_Test
exec sp_ProcedureA

will return all the 40 fields. Not allowed to user the table name from Database A, so select statements are ruled out.

Cursors are also a big no. Not my decision though.

Thanks in advance.

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-02 : 09:48:18

[DEF CON 1]
I have been unable to come up with a solution to the problem mentioned below. Hope someone could point me in the right direction.
If this is not the right forum, please point me to the right one.
[/DEF CON 1]

Be glad to.

[DEF CON 2]
Have a database (Database A) and a stored procedure (spr_ProcedureA) that returns about 40 fields. Access to the tables in Database A are strictly through this procedure.
[/DEF CON 2]

Whoa...we talking dynamic SQL here?

[DEF CON 3]
I have another database(Database B) residing on another server from where I will have a procedure call/exec the sp_ProcedureA in Database A from Database B and insert the resultset in a staging table in Database B. Need only 7 fields of the 40 returned.
[/DEF CON 3]

You're returning 40 columns every time when you only need 7?

[DEF CON 4]
Question: How will I exec and insert in the staging table selective columns when no table names(from Database B) are not to be used.
[/DEF CON 4]

You need to totally rethink your architecture

[voice of reason]
Cursors are also a big no. Not my decision though.
[/voice of reason]

Well there's some common sense here.

Is this a homework assignment that's gone wrong?

[/quote]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources