How to pass a dataset as a output parameter between stored procedures t-sql

Question

I created a stored procedure that has input the DBName and output param. a flag that indicates if the descriptions of tables and columns were downloaded into a temp table with success. I try to pass the temp table as output param and use it in another procedure. I tried with udt table and openrowset but it didnt work. Can anyone help me out with this problem. Thanks.

Code :

declare @vFlagOk tinyint
declare @vDataSet nvarchar(max)

exec dbo.Documenter_setDescriptionFromDB 
    @pDBName = 'Name'
    , @pSQLCMDRunedWithSuccess = @vFLagOK output , @pDataSetDescriptions = @vDataSet output

Can you post a little code to describe what you're trying to do? — lc., Jun 23 '14 at 07:34
I believe you'll have to use a Table-Valued-Function for this: http://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx — NickyvV, Jun 23 '14 at 07:34
Nicky is there any other alternative? I can not use function to solve this problem. — Dani Dărăban, Jun 23 '14 at 07:46
you could make the temp table global (##) and access it via the 2nd SP. — g2server, Jun 23 '14 at 08:03
Thanks for your solution i will use a global temp, best regards — Dani Dărăban, Jun 23 '14 at 08:31

t-clausen.dk · Answer 1 · 2014-06-24T06:58:20.517

2

I don't think passing Types(tables) as an output parameter is possible since they must be ReadOnly. However Normal procedure is selecting the data inside the procedure, this data can be inserted into a table while calling the procedure. You can pass a Type(a type declared as table) to a procedure. Here is an example:

Create type:

CREATE TYPE MyType AS TABLE  
    ( Var1 INT, Var2 INT );
GO

Create procedure:

CREATE procedure p_myproc 
(
  @x MyType readonly
)
as
BEGIN

  SELECT 
    Var1, Var2
  FROM 
    @x
  UNION ALL
  SELECT
    0,0
END

Script to call:

DECLARE @outputtype MyType
DECLARE @inputtype MyType
INSERT @inputtype VALUES(1,1)

INSERT @outputtype(Var1, Var2)
EXEC dbo.p_myproc @inputtype

SELECT 
  Var1,
  Var2
FROM
  @outputtype

Result:

Var1 Var2
1    1
0    0

edited Jun 24 '14 at 06:58

answered Jun 23 '14 at 08:37

t-clausen.dk

43,517
12
59
92

good answer, but there's an error with the syntax: Val1,Val2 in the 3rd script block should be Var1,Var2 – g2server Jun 24 '14 at 06:24
@g2server thank you it has been corrected. I originally changed * to VaL1 and Val2. Should have been as you said Var1 and Var2. – t-clausen.dk Jun 24 '14 at 07:00

How to pass a dataset as a output parameter between stored procedures t-sql

1 Answers1