how to get rid of the cursor


  I made a function in MSSQL 2008 that uses a cursor to iterate a table, do some string manipulations and return a table.

Here is the source table:
ID      GroupID      TAG
252      40      Draxis L2.Alarms.alrms
253      41      Draxis L2.Run.cpt trig
254      41      Draxis L2.Run.plc status
255      41      Draxis L2.Run.ppm
256      41      Draxis L2.Run.trig read
257      41      Draxis L2.Run.trig write
258      42      Draxis L2.Setup.phc1 response read
259      42      Draxis L2.Setup.phc1 response write
260      42      Draxis L2.Setup.timer plc acc
261      42      Draxis L2.Setup.timer plc pre
262      42      Draxis L2.Setup.trig delay read
263      42      Draxis L2.Setup.trig delay write
264      40      Draxis L2.Alarms.alrms sys
265      41      Draxis L2.Run.plc start
266      41      Draxis L2.Run.plc stop
267      41      Draxis L2.Run.reset alrms
268      41      Draxis L2.Run.reset cntrs
269      41      Draxis vs setup

The TAG field represent OPC Server's group. I want to retrieve all TAG's that refers to OPC group "Alarms", and I want to result to be in a table, something, in this case, like:
GROUP               ITEM
Alarms               alrms
Alarms               alrms sys

If I want to get the groups refering to "Setup", I'd get something like:
GROUP                ITEM
Setup                  phc1 response read
Setup                  phc1 response write

so I made the following function:
ALTER FUNCTION [dbo].[FX_GetGroupOPCItem](
  @ListSep varchar(1),
  @GroupName VARCHAR(50)
  Groupe VARCHAR(50) NOT NULL,
  Item varchar(50) NOT NULL
  DECLARE @Tmp  varchar(8000),
          @Item varchar(50),
          @Pos INT,
          @cursor_TAG VARCHAR(100)


  OPEN tag_cursor
  FETCH FIRST FROM tag_cursor
    INTO @cursor_TAG


                           SET @Tmp = RTrim(LTrim(@cursor_TAG))
            -- enleve le premier champ
            SET @Pos = CHARINDEX(@ListSep, @Tmp, 1)
            IF @Pos = 0
            SET @Tmp = SUBSTRING(@Tmp, @Pos+1, LEN(@Tmp))

            SET @Pos = CHARINDEX(@ListSep, @Tmp, 1)
            IF @Pos = 0 BEGIN
               SET @Item = @Tmp
               SET @Tmp = ''
            ELSE BEGIN
               SET @Item = SubString(@Tmp, 1, @Pos-1)
               SET @Tmp = SubString(@Tmp, @Pos+1, Len(@Tmp)-@Pos)

            IF @Item = @GroupName
                  INSERT INTO @Result
                        VALUES (@Item, @Tmp)

            FETCH NEXT FROM tag_cursor
                  INTO @cursor_TAG

this works fine. But I'm wondering, how can I get rid of the cursor in this case? I'd like to get better at query optimization and I thought that would be a good starting point.

thanks for your time and help
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

So you always have this structure "xxxx.xxxx.xxxx"? (That is three levels). If so, try this:

select parsename(TAG + '.hhhh', 3) as TAG, parsename(TAG + '.hhhh', 2) as Item
from yourtable
where groupID = 40

Open in new window

Dominic34Author Commented:
thanks for the reply.

it works partially. I can't uses the where clause, because it's a parameter (@GroupName) who tell which group I want to retrieve (alarms, setup, run, warning, etc). and groupID will change on every database anyway.

can you explain how the parsename works? what means the parameters?
do it like this then:
For more info on the PARSENAME function, check this link: 
 But basically parsename works with four levels ( Because you only have three then I've added and extra dummy one ".hhhh" there so you can parse the string as you need.

select parsename(TAG + '.hhhh', 3) as TAG, parsename(TAG + '.hhhh', 2) as Item 
from yourtable 
where parsename(TAG + '.hhhh', 3) = @groupname

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dominic34Author Commented:
works like a charm!!!

thanks a lot!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.