July 25, 2005 at 7:57 pm
hi,
i copy/pasted the actual code of the SP below. can anyone translate it into something that does not use cursor?
CREATE PROCEDURE TelstraData_Form @paramNumber varchar(30)
AS
declare @brief_description varchar(920)
declare @number varchar(30)
declare @work_order_no varchar(110)
declare @work_order_no1 varchar(50)
declare @work_order_no2 varchar(60)
declare @incident_id varchar(30)
declare @work_item_id varchar(60)
declare @work_item_description varchar(210)
declare @unit_cost float
declare @work_item_quantity float
declare @works_type varchar(60)
declare @sc_addr1 varchar(100)
declare @sc_addr2 varchar(100)
declare @varDescription varchar(8000)
declare @description varchar(2000)
declare @category varchar(60)
declare @request_phone varchar(60)
declare @requested_by_first varchar(40)
declare @requested_by_last varchar(90)
DELETE tempo_telstraData
DECLARE Quotation_Cursor CURSOR FOR
SELECT dbo.cdminvoicingm1.work_item_id, dbo.cdminvoicingm1.work_item_description, dbo.cdminvoicingm1.unit_cost,
dbo.cdminvoicingm1.work_item_quantity, dbo.cdminvoicingm1.works_type, dbo.cm3rm1.number, dbo.cm3rm1.category, dbo.cm3rm1.brief_description,
dbo.cm3rm2.work_order_no, dbo.cm3rm2.incident_id, dbo.cm3rm2.work_order_no1, dbo.cm3rm2.work_order_no2, dbo.cm3rm1.request_phone,
dbo.cm3rm1.requested_by_first, dbo.cm3rm1.requested_by_last
--, dbo.cm3ra4.description
FROM dbo.cm3rm1 LEFT OUTER JOIN
dbo.cdminvoicingm1 ON dbo.cm3rm1.number = dbo.cdminvoicingm1.change_number INNER JOIN
dbo.cm3rm2 ON dbo.cm3rm1.number = dbo.cm3rm2.number
--INNER JOIN dbo.cm3ra4 ON dbo.cm3rm1.number = dbo.cm3ra4.number
WHERE (dbo.cm3rm1.category = 'CDM Quote') AND
--(dbo.cm3ra4.record_number = 1)
cm3rm1.number = @paramNumber
OPEN Quotation_Cursor
FETCH NEXT FROM Quotation_Cursor INTO @work_item_id,@work_item_description,@unit_cost,@work_item_quantity,@works_type,@number,@category,@brief_description,
@work_order_no,@incident_id,@work_order_no1,@work_order_no2,@request_phone,@requested_by_first,@requested_by_last
WHILE @@FETCH_STATUS = 0
BEGIN
SET @varDescription = ''
DECLARE Description_Cursor CURSOR FOR
SELECT dbo.cm3ra4.[description] FROM dbo.cm3ra4 WHERE dbo.cm3ra4.number = @number
OPEN Description_Cursor
FETCH NEXT FROM Description_Cursor INTO @description
WHILE @@FETCH_STATUS = 0
BEGIN
SET @varDescription = @varDescription + @description + char(13) + char(10)
FETCH NEXT FROM Description_Cursor INTO @description
END
INSERT INTO tempo_telstraData (work_item_id,work_item_description,unit_cost,work_item_quantity,works_type,number,category,brief_description,
work_order_no,incident_id,work_order_no1,work_order_no2,request_phone,requested_by_first,requested_by_last,xdescription)
VALUES (@work_item_id,@work_item_description,@unit_cost,@work_item_quantity,@works_type,@number,@category,@brief_description,
@work_order_no,@incident_id,@work_order_no1,@work_order_no2,@request_phone,@requested_by_first,@requested_by_last,@varDescription)
CLOSE Description_Cursor
DEALLOCATE Description_Cursor
FETCH NEXT FROM Quotation_Cursor INTO @work_item_id,@work_item_description,@unit_cost,@work_item_quantity,@works_type,@number,@category,@brief_description,
@work_order_no,@incident_id,@work_order_no1,@work_order_no2,@request_phone,@requested_by_first,@requested_by_last
END
CLOSE Quotation_Cursor
DEALLOCATE Quotation_Cursor
select * from tempo_telstraData
GO
thanks
ann
July 26, 2005 at 1:54 am
Could you post definitions of all tables involved and a description of what you are trying to achieve? Some sample data would also be helpful.
July 26, 2005 at 2:34 am
hi,
pls have a look at this..u can do away with the cursor..
CREATE PROCEDURE TelstraData_Form @paramNumber varchar(30)
AS
INSERT INTO tempo_telstraData (work_item_id,work_item_description,unit_cost,
work_item_quantity,works_type,number,category,
brief_description,
work_order_no,incident_id,work_order_no1,work_order_no2,
request_phone,requested_by_first,requested_by_last,xdescription)
SELECT dbo.cdminvoicingm1.work_item_id,
dbo.cdminvoicingm1.work_item_description,
dbo.cdminvoicingm1.unit_cost,
dbo.cdminvoicingm1.work_item_quantity,
dbo.cdminvoicingm1.works_type,
dbo.cm3rm1.number, dbo.cm3rm1.category,
dbo.cm3rm1.brief_description,
dbo.cm3rm2.work_order_no, dbo.cm3rm2.incident_id,
dbo.cm3rm2.work_order_no1, dbo.cm3rm2.work_order_no2,
dbo.cm3rm1.request_phone,
dbo.cm3rm1.requested_by_first, dbo.cm3rm1.requested_by_last
, dbo.cm3ra4.description
FROM dbo.cm3rm1 LEFT OUTER JOIN
dbo.cdminvoicingm1
ON dbo.cm3rm1.number = dbo.cdminvoicingm1.change_number
INNER JOIN dbo.cm3rm2
ON dbo.cm3rm1.number = dbo.cm3rm2.number
INNER JOIN dbo.cm3ra4 ON dbo.cm3rm1.number = dbo.cm3ra4.number
WHERE (dbo.cm3rm1.category = 'CDM Quote') AND
cm3rm1.number = @paramNumber
i see the inner cursor is used just to retreive the descriptons..Rite? u can use that table within ur prior select statement and do away with the cursor...sorry, if there are any typo mistakes in my query becoz i didnt get a chance to execute it..Hope that helps
Regards,
Dilip
July 26, 2005 at 7:07 am
Provided your using sql2k you could create a function to return the concatenated descriptions and use it instead of the cursor
Far away is close at hand in the images of elsewhere.
Anon.
July 27, 2005 at 11:36 am
The description-concatenation function could be:
CREATE FUNCTION dbo.ufnConcatDescription (@number as varchar(30))
RETURNS varchar(8000)
AS BEGIN
DECLARE @varDescription varchar(8000)
SET @varDescription = ''
SELECT @varDescription = @varDescription + dbo.cm3ra4.[description] + char(13) + char(10)
FROM dbo.cm3ra4 WHERE dbo.cm3ra4.number = @number
{ ORDER BY ??? }
RETURN @varDescription
END
Then your whole proc can be replaced by:
DELETE tempo_telstraData
INSERT INTO tempo_telstraData (...)
SELECT ..., dbo.ufnConcatDescription(number)
FROM dbo.cm3rm1 ...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply