Calling a stored procedure from another one

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

SQL Server Development (2000)

Calling a stored procedure from another one

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-31 : 08:15:12

Dhruba writes "How to call a (child) stored procedure which returns some data(selects something from a table) , from a (parent) stored procedure which inserts those data (returned by the child one)?"

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-01-31 : 08:31:26

Weeeeeeeeeeeeeeellllllllllll, if I read your English right (and there's a good possibility I didn't), then:


USE Northwind
GO

CREATE TABLE test_insert(data VARCHAR(55))
GO

INSERT test_insert(data)
	SELECT 'URRRR?'

SELECT data FROM test_insert
GO

CREATE PROCEDURE select_stuff

AS

SELECT data FROM test_insert WHERE data = 'URRRRRRR?'
GO

CREATE PROCEDURE master_stuff

AS

INSERT test_insert(data)
	SELECT 'URRRRRRR?'
EXEC select_stuff
GO

EXEC master_stuff
GO

DROP TABLE test_insert
DROP PROCEDURE select_stuff
DROP PROCEDURE master_stuff
GO

Nothing magical!!!!

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

SqlStar
Posting Yak Master

121 Posts

Posted - 2005-02-01 : 04:50:34

Hi,

You can achieve your task using User Defined Functions instead of stored procedure. Just create a function, it should contain that "child" stored procedure's select statement. See the following example for your idea.

USE pubs
GO

CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
FROM sales s, titles t
WHERE s.stor_id = @storeid and
t.title_id = s.title_id)
GO

Create Table Test(title varchar(100), qty int)
GO

Insert Into Test
Select * From dbo.SalesByStore(6380)
GO

Select * from Test
GO

:) While we stop to think, we often miss our opportunity :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources