Using CLR function

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » CLR Integration and Programming. » Using CLR function

Using CLR function

Rate Topic

Display Mode

Topic Options

Author

Message

naveenreddy.84

Posted Tuesday, May 12, 2009 2:11 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 3:36 AM
Points: 54, Visits: 259

Hi All,
I have a Table1 containing bit values '0' and '1'.In need to combine this column value to a bit array say 11011 and perform a bitwise OR operation aganist a column in another table2 which stores correspoding bit sequence in varchar data type variable.

I can achieve the same through .net code. I,e creating a bit array from the column values of Table1 and store it into a bitarray type variable. And performing the bitwise operation aganist the another table's column.

Kindly let me know if there is a need for creating CLR function or is there a way to achieve the same Sql Server.

Regards,

Post #715449

Ale Pelc

Posted Tuesday, May 12, 2009 2:50 PM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 10:59 AM
Points: 1,109, Visits: 523

Hi,
If I correctly understood what you want to achieve, I think you can perform that using SQL, which I think will be better than deploying a CLR.

declare @value1 bit
declare @value2 varchar(10)
set @value1 = 0
set @value2 = '10001'
select @value1|cast(@value2 as int)

The result for that will be 10001.

Cheers

Alejandro Pelc

Post #715479

RBarryYoung

Posted Tuesday, May 12, 2009 2:56 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 11:17 AM
Points: 9,855, Visits: 9,376

The arithmetic and logical expressions to do this are pretty easy (it's "Power(..)", "|" and "+" mostly), however, what not clear is where the multiple bits are coming from in your example and how they should be orded and aligned. If you could provide a small example (say 3 or more bits) of the source table and rows and the desired result, I am sure that we can give you a good starting example.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #715486

naveenreddy.84

Posted Tuesday, May 12, 2009 9:15 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 3:36 AM
Points: 54, Visits: 259

Hi,

My table1 consist of below values

ResID status(bit data type)
1 1
2 0
3 1

Table2 contains

Rule Rvalues(bit value stored as a string) status(bit)
1 101 0
2 110 1
3 001 0

In need to create an array of bits from the first table in a particular sequence order(asc order of ResID) i,e 101. This value i need to compare with each value in Table2 by performing the bitwise OR operation and the get the status from the table2. In our case final value i will get is status=0.

the values in table1 may increase to 100 as well.

This is summary of things that needs to be done:
1) Creating an array of bits.
2) Retrieving the values from table2 one by one in a butarray format.
3) Performing the bitwise OR operation.
4) fetch the exact status value from table2

Further, also let me know in which scenario the CLR based stored proceduers, triggers etc implemention are used.

Regards,

Post #715608

RBarryYoung

Posted Tuesday, May 12, 2009 9:47 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 11:17 AM
Points: 9,855, Visits: 9,376

Well it still seems like something is missing, but this code does what you have specified:

;WITH cteBinary as (
	Select 
	  SUM(
		Case [status] 
			When 1 Then Power(2, ResID) 
			Else 0 End
		) as [Bitmask]
	 From Table1
)
SELECT
  t2.Rvalue | b.BitMask as [Result]
, [status]
 From Table2 t2
  Cross Join cteBinary b

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #715617

RBarryYoung

Posted Tuesday, May 12, 2009 9:57 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 11:17 AM
Points: 9,855, Visits: 9,376

naveenreddy.84 (5/12/2009)

Further, also let me know in which scenario the CLR based stored proceduers, triggers etc implemention are used.

The three general cases for SQLCLR use are:

1) Access to external resources not accessible from T-SQL.

2) To insure a common codebase is used on both client and server.

3) When the computation per column or byte is fairly high.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #715625

naveenreddy.84

Posted Monday, May 18, 2009 2:16 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 3:36 AM
Points: 54, Visits: 259

1) What scenario is best suited for implementing CLR triggers?
2) What are things that needs to be taken care of while implementing the CLR tiggers

Post #718863

Florian Reischl

Posted Monday, May 18, 2009 3:08 AM

SSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932

naveenreddy.84 (5/18/2009)

1) What scenario is best suited for implementing CLR triggers?

As Barry already wrote, accessing external resources like a HTTP post/request.

2) What are things that needs to be taken care of while implementing the CLR tiggers

Threading

The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems

Post #718891

sudevpdas

Posted Monday, June 25, 2012 10:25 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, June 13, 2013 2:36 AM
Points: 29, Visits: 152

Hi,

I am exepriencing performance issues while performing bitwise operation in sql server. I am planning to use a .net clr function for the same. what are your thoughts on that?.

Thanks,
Sudhev Das

Post #1320999

« Prev Topic | Next Topic »

Permissions