How to do further manipulation on sql procedure records using CLR RRS feed

  • Question

  • Hello everyone. I am newbie to CLR integration while developing an understanding to CLR integration in c#.net for stored procedure in sql server 2008 i am caught in a problem. for a better understanding first take a look at this fictitious data.

    Qty   price 

    2        10

    3         9

    4         20

    now i have this data in my store procedure's query  on which i would like to perform further manipulation using CLR integration which is to Calculate Total=Qty*Price for each row for sake of understanding CLR integration.

    once the calculation in managed code is done then i would like sql server to the following as resultset not as a message.

    Though i have achieved this with SqlDataRecord which give me one record/ per resultset which is not what i want. I want these three rows to occur like following in only one result set.

    Qty   price    Total

    2        10        20

    3         9         27

    4         20       80

    How can i achieve this in CLR integration using c#.net.
    Your guidance will greatly be appreciated.

    Thanks in Advance.

    Hamad Salahuddin Elite Technologies,Abbottabad Pakistan www.et.com.pk

    Wednesday, February 13, 2013 5:54 PM


  • Hi Hamad,

    Although this is interesting from a point-of-view of learning SQLCLR, the MUCH better way to approach the problem is to use the T-SQL query "SELECT Qty, Price, Qty*Price AS Total from Yourtable". For the sake of understanding SQLCLR, the way you'd do this is (assuming that you are getting the original three rows back through a SqlDataReader):

    1. Create a 3-member array of SqlMetaData, with data types corresponding to the data that you read (first 2 columns) and the data type of the third column which you choose.
    2. Create a SqlDataRecord from the SqlMetaData array.
    3. Call SqlPipe.SendResultsStart with the SqlMetaData.
    4. In a loop, retrieve each row using the SqlDataReader, calculate the third column, fill in all the SqlDataRecord columns using the appropriate setter methods and call SqlPipe.SendResultsRow.
    5. When you are done, call SqlPipe.SendResultsEnd.

    See the example here: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.sqlpipe.sendresultsstart.aspx. If you are getting only one row, you're probably missing step 4 above. If you still have problems, please post your SqlDataRecord/SqlPipe code.

    Cheers, Bob

    • Marked as answer by nielsbMVP Thursday, February 14, 2013 3:20 AM
    Wednesday, February 13, 2013 10:54 PM