SQL Server R2--t-sql stored procedure return table

Not finished, feeling a bit of a problem.

================================== Declaration ==================================

This article is original, reproduced in the text of the influential annotated author and source, and to ensure the integrity of the article.

Do not modify (including this statement) without the consent of the author, and reserve the right of legal investigation.

Do not use for publication, printing or academic reference without the consent of the author.

In order to ensure the correct contents, we suggest that we should read the original text.

This article link: http://www.cnblogs.com/wlsandwho/p/4374367.html

=======================================================================

Although I learned SQL Server 2005 early in school, I didn't use it personally at work.

Recent projects have abandoned access and used SQL Server 2008.

So we need to write the stored procedure.

(I don't know why they like access so much, it's easy to deploy, most industries just keep the data.) ）

=======================================================================

Read a few days (function words) of the information, can only write this degree, or hope to have the guidance of the Great God.

=======================================================================

A table is created in the database to hold the data that was last processed. When the software starts, read the data to the interface and then empty the table.

Before I used ADO in VC + + in 2 steps: In the transaction, read the table data, delete the table data.

No problem. But feel good trouble.

To see if I could do it directly with a stored procedure, I looked at the following manual:

|  The text,ntextimage parameter cannot be used as an OUTPUT parameter. The OUTPUT parameter can be a cursor placeholder, except for CLR procedures.
You cannot specify a table-valued data type as the OUTPUT parameter of a procedure.

It seems that only the curve of salvation.

========================= the first time to write this kind of thing, but also embarrassed ==============================

Create a user-defined table type

1 CREATETYPE Tbldata as TABLE2 (3 [CMD] [nvarchar]( -),4 [Num] [nvarchar]( -),5 [Name] [nvarchar]( -) ,6 [Innerid] [nvarchar]( -),7 [Result] [nvarchar]( -),8 [EndTime] [datetime],9 [Legal] [tinyint]Ten ) One  A GO

Create a stored procedure

1 CREATE PROCUsp_getdata2  as 3     BEGIN4         DECLARE    @tblSDTbldata5         6         BEGIN TRANSACTION7             INSERT   into @tblSD8 (CMD,9 Num,Ten Name, One Innerid, A Result, - EndTime, - Legal the                     ) -                     SELECTCMD, - Num, - Name, + Innerid, - Result, + EndTime, A Legal at                      fromData -  -             DELETE     fromData -         COMMIT TRANSACTION -          -         SELECT    * in          from    @tblSD -     END

Don't forget to authorize.

1 GRANT EXECUTE  on usp_ to Testdev

=======================================================================

Tried, the function is realized, the feeling efficiency will pit, fortunately the data is very few.

No way, let's talk about it later.

(Has been disinfected, so naming norms what, Virgo please self-esteem.) )

SQL Server R2--t-sql stored procedure return table