How to pass the Table Datatype between stored 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

SQL Server Development (2000)

How to pass the Table Datatype between stored procedure...

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-01-29 : 08:53:45

Ram writes " We are trying to execute a stored procedure within another
stored procedure.

The "calling" stored procedure needs to pass a variable
(declared as datatype table) to the "called" stored procedure.

We are not able to do this. (In oracle we can pass the variables declared as %ROWTYPE between stored procedures).

Can you provide an example on how to do this
IN SQL SERVER 2000 SP3

Thanks
Ram"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-29 : 08:57:38

Use a temporary table instead:

CREATE PROCEDURE mySP1 AS
SET NOCOUNT ON
CREATE TABLE #tempTable (col1 int, col2 varchar(10))
INSERT INTO #tempTable VALUES(1, 'Hello')
INSERT INTO #tempTable VALUES(2, 'There!')
EXECUTE mySP2
GO

CREATE PROCEDURE mySP2 AS
SET NOCOUNT ON
SELECT * FROM #tempTable
DROP TABLE #tempTable
GO

#tempTable will be visible to mySP2, as long as you run mySP1. You can't run mySP2 by itself because it does not create #tempTable itself.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources