Sending custom resultset -- SQL Server 2005

This article would explain in detail (with complete code sample) the way to return custom resultsets to the end user using CLR in SQL Server 2005.

Things to know before we get started:

1) SqlPipe() -- Its the job of the SqlPipe object to send results back to the stored proc or for that matter UDF etc.,

2) SqlDataRecord -- If we want to return resultset (more than one record) make use of this new object which is introduced in ASP.NET 2.0. For that we need to first create a schema using SqlMetaData objects.

With this small introduction lets get our hands dirty by writing a small SP which would return more than records.

1. Open VS.NET 2005
2. Create C# based Database project
3. Right click on the solution and choose Add >> Stored proceedure >> name the file as SPReturningResultSet

4. Copy paste the below code into that file

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;
using System.Data.SqlClient;

public partial class StoredProcedures
{
[SqlProcedure]
public static void SPReturningResultSet()
{
SqlMetaData[] objMetaDataCols = new SqlMetaData[3];
objMetaDataCols[0] = new SqlMetaData("Sno", SqlDbType.Int);
objMetaDataCols[1] = new SqlMetaData("FirstName", SqlDbType.VarChar, 50);
objMetaDataCols[2] = new SqlMetaData("EmailID", SqlDbType.VarChar, 50);

SqlPipe objPipe;
objPipe = SqlContext.GetPipe();

SqlDataRecord objRows = new SqlDataRecord(objMetaDataCols);

objRows.SetInt32(0, 1);
objRows.SetString(1, "Vadivel Mohanakrishnan");
objRows.SetString(2, "vmvadivel@gmail.com");

// New result-set starts here
objPipe.SendResultsStart(objRows, true);

// In-between add as many rows as you want
objRows.SetInt32(0, 2);
objRows.SetString(1, "Maruthiraja");
objRows.SetString(2, "mars@gmail.com");
objPipe.SendResultsRow(objRows);

objRows.SetInt32(0, 3);
objRows.SetString(1, "Sriram");
objRows.SetString(2, "nilapenn@gmail.com");
objPipe.SendResultsRow(objRows);

// End of the result-set code
objPipe.SendResultsEnd();
}
};

Build the project (Ctrl + shift + B) in VS.NET 2005 and then open up SQL Server 2005 and do the following:

Create Assembly ResultSetDemoFrom 'C:\Documents and Settings\Administrator\My Documents\Visual Studio\Projects\SqlServerProject1\SqlServerProject1\bin\Debug\SqlServerProject1.dll
'WITH PERMISSION_SET = EXTERNAL_ACCESS;
Go

Create Proc dbo.uspResultSetDemo
As External name ResultSetDemo.StoredProcedures.SPReturningResultSet
Go

Test our SP:

Exec dbo.uspResultSetDemo

If at all it throws an error like the one shown below .. then execute the execute the two lines of code beneath it:

Msg 6263, Level 16, State 1, Line 1
Execution of user code in the .NET Framework is disabled. Use sp_configure 'clr enabled' to enable execution of user code in the .NET Framework.

sp_configure 'clr enabled', 1
Reconfigure with override

Clean Up:

drop proc dbo.uspResultSetDemo
go

drop assembly ResultSetDemo
go

For more reading on this concept, I recommend you to go through this MSDN link >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/mandataaccess.asp