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

sql running in Access 2010 but not sql server 2012

sql sql-server ms-access

Hi the code below works in access (part of line 24 was added for sql server = [Old_DB].[dbo].) but not in Sql Server 2012

FileIOPermission SecurityException when running Xunit in TFS

c# tfs securityexception xunit

I'm attempting to integrate Xunit tests into our TFS build.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.