SELECT from Stored Procedure in SQL Server

What is Select from Stored Procedure?

Many times while writing T-SQL scripts you must have stored data of your Select query into a temporary table, like in the below query.

SELECT * INTO TempUserTables FROM SYS.OBJECTS WHERE TYPE_DESC = 'USER_TABLE'

Here for example purposes, I have taken the SYS.OBJECTS table

But if you try to do the same for a stored procedure, SQL server will give an error to you. To get an idea, please look at the below example:

SELECT * INTO LoginData FROM (Exec SP_WHO)

If you try to execute the above query you will get this error:

‘Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'Exec'.

Now, if such a kind of operation is not allowed in SQL Server, how can it be possible to use the result set from a stored procedure in a Select statement? Of course we can use INSERT –EXEC method (i.e., INSERT INTO YourTempTable (Col1, Col2) EXEC yourSP). But everything has its pros and cons. The INSERT-EXEC method may look easier to implement but if your SP’s output changes you will need to change your table’s structure .This kind of rigid coding is not acceptable. We came across the same problem in our project.

Why did I upload this code snippet? 

Our application processes different kinds of account opening forms of a bank. At EOD we generate separate Excel reports for each form type. To fetch data of processed forms we have created stored procedures (i.e., a single stored procedure for a single form type). Our client asked for a consolidated Excel report which will cover all form types .We thought about different solutions. Some suggested creating a whole new stored procedure, others suggested creating a master sp which will call other SPs (which we have already created for each form type) and giving a consolidated output. While implementing a master SP solution we faced the above mentioned problem. After a lot of R & D work we decided to implement a master SP using a CLR stored procedure technique.

Basics of CLR Stored Procedures  

CLR is the abbreviation of Common Language Runtime. In SQL Server 2005 and its later versions you can write stored procedures, triggers, user-defined types, user-defined functions, user-defined aggregates, and streaming table-valued functions, using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#. CLR is faster than T-SQL in many cases. CLR is mainly used for tasks which are not possible by T-SQL. To get a basic knowledge of CLR stored procedures, please follow the below links:

• Getting Started With CLR Stored Procedures
• Basics of a CLR Stored Procedure

Creating a CLR Stored Procedure

With the assumption that you have read the above mentioned links (i.e., if you are novice to the field of CLR stored procedure), I have explained the code below:

1. Create a table in your development database name: UserLoginData (this will hold the selective data of your stored procedure, in our case, sp_who)
2. Create another table named getConnectionString (this is to store a regular connection string, which will be used in the SQLBulkCopy instance)
3. Create a CLR Stored Procedure named USP_Select_Into_Table_FromSP.
4. Insert the following code snippet. Here I have used "CONTEXT CONNECTION=TRUE". Because the context connection is an in-process–only connection, it can contact the server "directly" by bypassing the network protocol and transport layers to send Transact-SQL statements and receive results. For more details about context connection, go through Details of Context Connection and Regular Connection .
C#

DataTable objSourceDataTable = new DataTable();
SqlDataAdapter objDataAdapter = new SqlDataAdapter();
string strConnectionString = null;
SqlDataReader objDataReader = null;

using (SqlConnection objConn = new SqlConnection("CONTEXT CONNECTION=TRUE"))
{
	objConn.Open();

	SqlCommand objSelectCommand = new SqlCommand();
	objSelectCommand.CommandType = CommandType.StoredProcedure;
    objSelectCommand.CommandText = 'sp_who';  
    objSelectCommand.Connection = objConn;
	objDataAdapter.SelectCommand = objSelectCommand;
	objDataAdapter.AcceptChangesDuringFill = false; 
	objDataAdapter.Fill(objSourceDataTable);
    try
    {
        SqlCommand objCmdGetConn = new SqlCommand();
        objCmdGetConn.CommandType = CommandType.Text;
        objCmdGetConn.Connection = objConn;
        objCmdGetConn.CommandText = "select ConnectionString  from    getConnectionString";
        objDataReader = objCmdGetConn.ExecuteReader();
    }

    catch (Exception ex1)
    {
        SqlContext.Pipe.Send(ex1.Message);
    }

    while (objDataReader.Read())
    {
    strConnectionString =  Convert.ToString(objDataReader["ConnectionString"]);
     strConnectionString = strConnectionString.Trim();
    }
    objConn.Close();  //Cotext Connection Closed
}//Context Connection Ends Here

5. As SQLBulkCopy cannot be used with Context Connection (for more details please see Restrictions of Context Connection), we will use a Regular Connection for this operation. For this we will fetch a regular connection string from the database using the below code.
6. In the previous step I mentioned that SQLBulkCopy cannot be used with Context Connection, but what does SQLBulkCopy mean? SQLBulkCopy is an efficient way of bulk loading of data into your table from another table or source. MSDN has illustrative material for SQLBulkCopy on link SQL Bulk Copy . Here I have used SQLBulkCopy for performance improvement. 
7. Now you have a regular connection string with you. Using this connection string, initiate the SQLBulkCopy object and then copy data from a DataTable to your table, i.e., in our case UserLoginData. Code snippet for this is as below:
C#

//Open Bulk Copy Session with regular Connectionstring 
using (SqlBulkCopy objBulkCopy = new SqlBulkCopy(strConnectionString))
{
    objBulkCopy.DestinationTableName = "UserLoginData";
    //objBulkCopy.ColumnMappings.Add(Source Column   Name,Destination Column Name)
    objBulkCopy.ColumnMappings.Add("SPID", "ULD_SPID");
    objBulkCopy.ColumnMappings.Add("Status", "ULD_Status");
    objBulkCopy.ColumnMappings.Add("Loginame", "ULD_Loginame");
    objBulkCopy.ColumnMappings.Add("Hostname", "ULD_Hostname");
    objBulkCopy.BatchSize = objSourceDataTable.Rows.Count;
    try
    {
        objBulkCopy.WriteToServer(objSourceDataTable);
    }
    catch (Exception ex)
    {
        SqlContext.Pipe.Send(ex.Message);
    }
    finally
    {
        objBulkCopy.Close();  //Close Bulk Copy Session
    }
}

8. You have done it. Just need to free some application memory. Do it using the following way:
C#

objDataAdapter.Dispose();
objSourceDataTable.Dispose();

9. Before compiling, there need to be some changes made in the permission level. Go to project properties -> Database -> Permission Level. Set permission level to External. If it is not set then you will get an exception as:

Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, 
  System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.".

10. For debugging purposes, you can write a script in TestScript as below:
SQL

exec USP_Select_Into_Table_FromSP
Select * from UserLoginData 

11. Now Build your solution, and deploy it. We will create a stored procedure ‘USP_Select_Into_Table_FromSP’ in your development database.

References

• http://msdn.microsoft.com/en-US/library/ms131094(v=SQL.90).aspx
• http://blog.sqlauthority.com/2008/10/19/sql-server-introduction-to-clr-simple-example-of-clr-stored-procedure/
• http://www.simple-talk.com/content/article.aspx?article=314
• http://www.simple-talk.com/sql/learn-sql-server/choosing-between-clr-and-t-sql-stored-procedures-a-simple-benchmark/