CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlstored-procedures
JR Lawhorne
JR Lawhorne

Reputation: 3302

Is it possible to pass a table valued parameter to a stored procedure and return a table result?

I'd like to create a stored procedure that takes a table argument and returns a separate (schema) table result set.

First, is this possible?

Second, am I on the right track or completely off?

My Procedure Definition

USE MyDatabaseName

-- drop the procedure so we can recreate it
IF (OBJECT_ID('doit')) is NOT NULL DROP PROCEDURE doit
GO

-- drop and recreate the type in case we want to change how its defined
IF TYPE_ID(N'MyTableType') IS NOT NULL DROP TYPE MyTableType
CREATE TYPE MyTableType AS TABLE(keyvalue bigint NOT NULL, datevalue date NOT NULL)
GO

-- create procedure
CREATE PROCEDURE doit ( @indata MyTableType READONLY )
AS

-- Ultimately, real work will be done here that accumulates
-- a result set and returns it.  For now, we just return
-- a sub-set of what we were passed to see if this will
-- work...Which, it doesn't seem to.

RETURN SELECT top 100 keyvalue, datevalue FROM @indata

GO

My calling code:

-- Call the doit procedure

declare @myargtable MyTableType

-- Gather my 'doit' argument
INSERT INTO @myargtable select top 1000 my_bigint_field, my_date_field from my_source_table;

-- This output is what I expect 'doit' to produce.
select top 100 * from @myargtable

-- Get a result table ready for the output of 'doit'
declare @results MyTableType

-- Store the results.
insert into @results EXEC doit @myargtable

-- I expect this to give me 100 records like above.
-- But, it gives me none.
select * from @results

Upvotes: 1

Views: 214

Answers (2)

lc.
lc.

Reputation: 116528

A stored procedure can only return an int as a return value.

Perhaps you meant to either simply SELECT your results to return them:

CREATE PROCEDURE doit ( @indata MyTableType READONLY )
AS
   SELECT top 100 keyvalue, datevalue FROM @indata

   RETURN 0

Or use a table-valued function instead:

CREATE FUNCTION doit ( @indata MyTableType READONLY )
RETURNS @t TABLE (
    keyvalue bigint,
    datevalue date )
AS

    insert into @t
    select top 100 keyvalue, datevalue
    from @indata

    return

Or even an inline function:

CREATE FUNCTION doit ( @indata MyTableType READONLY )
RETURNS TABLE AS RETURN (
    select top 100 keyvalue, datevalue
    from @indata
)

Upvotes: 1

RyanB
RyanB

Reputation: 757

Remove the RETURN from the SP definition so the line is just

SELECT top 100 keyvalue, datevalue FROM @indata

Upvotes: 3

Related Questions