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

Answers


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

SELECT top 100 keyvalue, datevalue FROM @indata

Need Your Help

How can I XML serialize an array of object lists?

c# xml exception xml-serialization

I get these errors when I try to XML serialize an array of lists.

How do I make an img stretch to its container's width, regardless of it's height?

css image

.. expecting the picture to get "cropped" at the top and bottom. I only want it to fit the width 100%, and wish to become bigger than the height, but not leave the certain container.