Translate this PL/SQL code into T-SQL for SQL Server.

  • Dear All,

    Its is very urgent , I am new to PL/SQL.

    Kindly help me to translate this PL/SQL code into T-SQL for SQL Server :

    DECLARE

    CURSOR C_ROW IS SELECT BARCODE, ITEM_NAME, LABNAME FROM DBO.FIR_STORY_MATNAME;

    BEGIN

    FOR ROW_REC IN C_ROW LOOP

    UPDATE DBO.MATERIALS SET MAT_NAME = ROW_REC.ITEM_NAME WHERE XMATNUMBER IS NULL AND MATTYPE= 'Undefined' AND MAT_NAME = ROW_REC.LABNAME;

    END LOOP;

    END;

    Regards,

    kathir.C

  • The cursor It works fine on Oracle but it is for a SQL Server 2008 DB that i need it.

    Please do reply anyone.

  • An exact translations would be something like this, just need to replace ..... in the variables with the data types of the fields. Also your not using BARCODE in that code snippet, I would remove it if its not being used.

    DECLARE @BARCODE ...., @ITEMNAME ....., @LABNAME.....

    DECLARE ACursor CURSOR FOR

    SELECT

    BARCODE,

    ITEM_NAME,

    LABNAME

    FROM

    dbo.FIR_STORY_MATNAME

    OPEN ACursor

    FETCH NEXT FROM ACursor INTO @BARCODE, @ITEMNAME, @LABNAME

    WHILE @@FETCH_STATUS = 0

    BEGIN

    UPDATE

    dbo.MATERIALS

    SET

    MAT_NAME = @ITEMNAME,

    WHERE

    XMATNUMBER IS NULL

    AND

    MATTYPE = 'Undefined'

    AND

    MAT_NAME = @LABNAME

    FETCH NEXT FROM ACursot INTO @BARCODE, @ITEMNAME, @LABNAME

    END

    CLOSE ACursor

    DEALLOCATE ACursor

  • The best thing to do here is to look up the syntax for cursors in Books Online, have a go at writing it yourself, and post back if there's anything you don't understand.

    Actually, that was the second best thing you can do. The best is to avoid using a cursor at all. It looks as if you could achieve this with a single UPDATE statement.

    John

  • Agreed. To give examples of how to do this more efficiently (this is true of SQL Server or Oracle), a couple of examples below:

    Using the "proprietary" UPDATE...FROM... syntax in SQL Server:

    UPDATE M

    SET MAT_NAME=F.ITEM_NAME

    FROM DBO.MATERIALS M

    INNER JOIN FIR_STORY_MATNAME F ON M.MAT_NAME = F.LABNAME

    WHERE XMATNUMBER IS NULL AND MATTYPE= 'Undefined';

    Using MERGE syntax (this works in SQL Server and Oracle):

    MERGE DBO.MATERIALS AS TARGET USING FIR_STORY_MATNAME AS SOURCE ON (SOURCE.LABNAME=TARGET.MAT_NAME)

    WHEN MATCHED AND (XMATNUMBER IS NULL AND MATTYPE= 'Undefined')

    THEN UPDATE

    SET MAT_NAME=SOURCE.ITEM_NAME;

  • Dear all,

    Thanks for your replies, Both Cursor and "proprietary" Update statement works perfectly.:-D

    Regards,

    Kathir.C

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply