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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ralmadaConnect With a Mentor Commented:
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
 
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
 
Dominic34Author Commented:
works like a charm!!!

thanks a lot!!!
0
All Courses

From novice to tech pro — start learning today.