MS SQL: Get returning data type info of a query

For years I’ve wondered if there’s a way to get the returning data types of a complex query or a stored procedure in Microsoft SQL Server. Something like:

sp_get_return_type_info select firstname, lastname from [user]

OR

sp_get_return_types StoreProcNameHere

I’ve done quick google searches for this many times in the past and this time I really dug deep. I mean like 3 hours deep just on this. And sadly, I could not find anything like that!!

But I did get something good out of this and I don’t want to forget it. I wondered how Visual Studio figures out the returning data types of complex queries that are added to a DataSet. So I ran the SQL Server Profiler as I was adding a new TableAdapter and I saw that they are also not using any magic sp_* call. They use this query to get the datatypes of each of the joined tables:

select col.name,  st.name as DT_name,   
  case when (st.name in ('nchar', 'nvarchar') and (col.max_length > 0))  
    then col.max_length / 2 else col.max_length end as max_length, 
  col.precision,  
  col.scale,  bt.name as BT_name,  col.is_nullable,  col.is_identity,
  col.is_rowguidcol,  
  OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst,   
  CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end) 
    as is_computed,  
  case when(cmc.column_id is null) then null else cmc.definition end 
    as formular,  
  col.collation_name,  col.system_type_id 
from campusvue_train.sys.all_columns col  
left outer join campusvue_train.sys.types st 
  on st.user_type_id = col.user_type_id 
left outer join campusvue_train.sys.types bt 
  on bt.user_type_id = col.system_type_id  
left outer join campusvue_train.sys.identity_columns idc 
  on idc.object_id = col.object_id and idc.column_id = col.column_id  
left outer join campusvue_train.sys.computed_columns cmc 
  on cmc.object_id = col.object_id and cmc.column_id = col.column_id  
where col.object_id = object_id('TABLE_NAME')  order by col.column_id

It’s just a nice join to have handy.

And VS is not able to figure out the datatypes of a table returned by a stored procedure. I tested this on a stored procedure that returned a select on a table variable as opposed to returning a select that came entirely from a normal database table; maybe it supports that, but I doubt it.

Running code after sending the http reponse to the client in ASP.NET

Let’s say you want to do a time consuming operation on the background
when a user hits a button on a webpage.

On the button click event add this code:

// A new thread is needed so the client does not have to 
// wait for the processing to be completed.
Thread t = new Thread(new ParameterizedThreadStart(TryToEmailAdmissionsRep));
t.Start(HttpContext.Current.User.Identity.Name);

Note that the code above calls the TryToEmailAdmissionsRep method. This
method takeas a parameter and that is why we’re doing using
ParameterizedThreadStart as opposed to just ThreadStart.

And there is one more trick that I know is needed when the website is running
from VS2008’s built in web server. I’m not sure if it’s needed on VS2010 or
when the website is running on IIS.

private void TryToEmailAdmissionsRep(object username)
{
    // This test line helps you see that the user
    // is really not waiting for the execution of this method.
    // Remove line after testing.
    System.Threading.Thread.Sleep(5000);

    // pliska: I added this try-catch because if an exception 
    // is thrown by this code, the line that threw the exception 
    // is executed over and over again throwing the exception 
    // over and over again in an infinite loop. This could be a
    // bug on the VS debugger but maybe it's actually happening
    // so I'm catching all exceptions.
    try
    {
        
        // This is where you would do the work. In this case
        // email the admissions representative.

    }
    catch (Exception ex)
    {
        EventLogger.LogAndEmailError(ex);
    }
}