Software Technology Tips

To return a custom recordset of values obtained during processing in a CLR procedure to the caller. You can create your records using the SqlDataRecord class and then send them through SqlPipe. Before you create an instance of SqlDataRecord, you need to define its structure as an array of objects of the SqlMetaData class:

SqlMetaData[] fields = new SqlMetaData[3];
fields [0] = new SqlMetaData("EmployeeID", SqlDbType.Int);
fields [1] = new SqlMetaData("VendorName", SqlDbType.NVarChar, 50);
fields [2] = new SqlMetaData("LeaseName", SqlDbType.NVarChar, 50);
fields [3] = new SqlMetaData("ContactDate", SqlDbType.DateTime);
fields [4] = new SqlMetaData("TotalAmount", SqlDbType.Money);

When the schema is finished, you will create an instance of SqlDataRecord and populate it with data:

SqlDataRecord record = new SqlDataRecord(fields);
record.SetInt32(0, 1001);
record.SetString(1, "LeaseLeaseLease Inc.");
record.SetString(2, "123-456-7890");
record.SetDateTime(3, DateTime.Now);
record.SetSqlMoney(4, 2000);

As the last step, you will send the record through the pipe to the caller:

SqlContext.Pipe.Send(record) ;