How stored procedure is processed by Sql Server and .Net

Question

I have been using stored procedure for more than 1.5 years. But I never thought how data is retrieved from UI or within another stored procedure.

When I writes a simple stored procedure. eg.

CREATE PROCEDURE sp_test
AS
BEGIN
 SELECT * FROM tblTest --Considering table has 3 columns.
END

How does C# gets this result into DataTable.

Whenever I have to use the result of this procedure into another procedure. I think we have to create a table valued parameter using table datatype and assign its result into table variable. I never tried it.

CREATE PROCEDURE sp_testcall
AS
BEGIN
 @temp = exec sp_test -- I think this would be the way, never tried
END

If above sample code is true, then what is the difference between using above method and Query to insert records into temporary table.

CREATE PROCEDURE sp_test
AS
BEGIN
 SELECT * INTO #tmp FROM tblTest --Considering table has 3 columns.
END

Since copying result into temporary table needs an another effort by sql server to do. But what would be going behind. Would it directly assigns reference of result into table valued parameter or do the same process as done by temporary table.

I might not be clear to you in my question. But will try to improve.

Answer 1

A select means "return data to client". C# is a client, therefore it gets the data.
Then again, it's not exactly C# that does it, it's ADO.NET. There's a data provider that knows how to use a network/memory/some other protocol to talk to the SQL server and read data streams it generates. This particular client (ADO.NET) uses the received data to construct certain classes, such as DataTable, other providers can do something completely different.
All that is irrelevant at SQL Server level, because as far as the server is concerned, the data has been sent out using the protocol with which the connection was established, that's it.

From inside, it doesn't make much sense to have a stored procedure return simply selected data to anything else.
When you need to do that, you have the means to explicitly tell SQL Server what you want, such as inserting the data into a temporary table available to both involved SPs, inserting data into a table-valued parameter passed to the procedure, or rewriting your stored procedure as a function that returns a table.

Then again, it's not exacly clear to me what you were asking about.

Answer 2

For an beginer to intermediate level you should always consider #temp tables and @table variables two faces of the same coin. While there are some differences between them, for all practical purposes they cost the same and behave nearly identical. The sole major difference is that @table variables are not transacted and hence not affected by rollbacks.

If you drill down into details, #temp tables are slightly more expensive to process (since they are transacted) but on the other hand @table variables have only the lifetime of a variable scope.

As to other issues raised by your question:

• table value parameters are always read only and you cannot modify them (insert/update/delete into them)
• tacking the result set of a procedure into a table (real table, #temp table or @tabel variable, doesn't matter) can only be done by using INSERT INTO <table> EXEC sp_test
• as a rule of thumb a procedure that produces a result that is needed in another procedure is likely to be better of as a User Defined Function

The topic of sharing data between procedures was analyzed at length by Erland Sommarskog, see How to Share Data Between Stored Procedures.