May 10, 2012 at 12:36 am
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
May 10, 2012 at 1:38 am
The cursor It works fine on Oracle but it is for a SQL Server 2008 DB that i need it.
Please do reply anyone.
May 10, 2012 at 1:48 am
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
May 10, 2012 at 1:49 am
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
May 10, 2012 at 1:56 am
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;
May 10, 2012 at 4:47 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy