how to get rid of the cursor

Hi,

  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 L2.Run.run 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
etc...

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

  DECLARE tag_cursor CURSOR LOCAL SCROLL FOR
  SELECT DISTINCT TAG FROM [TB_opcItems]

  OPEN tag_cursor
  FETCH FIRST FROM tag_cursor
    INTO @cursor_TAG

      WHILE @@FETCH_STATUS = 0 BEGIN

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

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

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

            FETCH NEXT FROM tag_cursor
                  INTO @cursor_TAG
      END
   
  RETURN
END

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
Dominic34Asked:
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.

ralmadaCommented:
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

0
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?
0
ralmadaCommented:
do it like this then:
For more info on the PARSENAME function, check this link:
http://msdn.microsoft.com/en-us/library/ms188006.aspx 
 But basically parsename works with four levels (xxx.xxx.xxx.xxx). 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

0

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!!!
0
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.