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:
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:
- Create a table in your development database name: UserLoginData (this will hold
the selective data of your stored procedure, in our case, sp_who)
- Create another table named getConnectionString (this is to store a regular connection string, which will be used in
the
SQLBulkCopy
instance) - Create a CLR Stored Procedure named USP_Select_Into_Table_FromSP.
- 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 .
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();
}
- 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. - 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. - 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:
using (SqlBulkCopy objBulkCopy = new SqlBulkCopy(strConnectionString))
{
objBulkCopy.DestinationTableName = "UserLoginData";
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();
}
}
- You have done it. Just need to free some application memory. Do it using the following way:
objDataAdapter.Dispose();
objSourceDataTable.Dispose();
- 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.".
- For debugging purposes, you can write a script in TestScript as below:
exec USP_Select_Into_Table_FromSP
Select * from UserLoginData
- Now Build your solution, and deploy it. We will create a stored procedure ‘USP_Select_Into_Table_FromSP’ in your development database.
References