Cursor update operation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Cursor update operation

Author

Topic

ankur_gurha
Starting Member

20 Posts

Posted - 2007-10-02 : 10:28:03

Hi,

I am looking for a good and efficient query.

I have 2 tables

SingleCompanyQuote

[dbo].[SingleCompanyQuote]
([SCQWorkflowID]
,[ComparisonQuoteID]
,[ProductVersionID]
,[RequestTypeID]
,[QuoteStatusID]
,[QuoteAndPrintStatusID]
,[NewBusiness]
,[ModifiedDate]
,[WorkflowStartTime]
,[ProviderRequestTime]
,[ProviderResponseTime]
,[DeleteStatus]
,[CompletedTimestamp])

And

[dbo].[ReAPDocument]
([DocumentGUID]
,[ReferenceID]
,[SingleCompanyQuoteID]
,[RemotePublishingAlertLogID]
,[DocumentTypeID]
,[DocumentStatusID]
,[IllustrationURL]
,[TransmissionDate]
,[ExpiryDate]
,[ReAPDocument]
,[DocumentDescription]
,[CreatingApp]
,[RetryNumber]
,[TransactionSize])

My stored procedure should be taking in 4 parameters

@SCQWorkflowId uniqueidentifier
, @QuoteAndPrintStatus int
, @IllustrationStatus int
, @URL varchar(max)

@Url is a comma separted string

and i need to perform a similar operation

UPDATE
dbo.SingleCompanyQuote
SET
QuoteAndPrintStatusID = @QuoteAndPrintStatus
WHERE
SCQWorkflowID = @SCQWorkflowId

IF EXISTS(
SELECT *
FROM dbo.ReAPDocument
INNER JOIN SingleCompanyQuote ON ReAPDocument.SCQWorkflowId = SingleCompanyQuote.SCQWorkflowId
WHERE SingleCompanyQuote.SCQWorkflowId = @SCQWorkflowId
AND IllustrationURL = @URL
)
BEGIN
UPDATE
dbo.ReAPDocument
SET
DocumentStatusID = @IllustrationStatus
FROM ReAPDocument
INNER JOIN SingleCompanyQuote ON ReAPDocument.SCQWorkflowId = SingleCompanyQuote.SCQWorkflowId
WHERE SingleCompanyQuote.SCQWorkflowId = @SCQWorkflowId
AND
IllustrationURL = @URL
END

ELSE
BEGIN

INSERT INTO
dbo.ReAPDocument (
SCQWorkflowID,
IllustrationURL,
DocumentStatusID
)
VALUES (
@QuoteAndPrintStatusID,
@URL,
@IllustrationStatus
)

END

for all the values in the comma seprated string .. I have a comma separated split function , but still i need to do this whole operation in some kind of cursor operation or something .. please help..as how can i update the ReAPDocument table with the appropriate value in a cursor or something..

Thanks in advance

Ankur

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-10-02 : 15:20:19

This is untested and there is probalby a syntax error on the INSERT, but I think it might get you in the right direction:

@SCQWorkflowId uniqueidentifier , 
@QuoteAndPrintStatus int , 
@IllustrationStatus int , 
@URL varchar(max) -- @Url is 
--	a comma separted string 
--	and i need to perform a similar operation 

DECLARE @UrlTable TABLE (Url VARCHAR(MAX)) -- Adjust MAX to a number if appropriate

INSERT @UrlTable
SELECT Val
FROM dbo.fnSplit(@URL) -- What ever split function you have

UPDATE 
	dbo.SingleCompanyQuote 
	SET QuoteAndPrintStatusID = @QuoteAndPrintStatus 
WHERE 
	SCQWorkflowID = @SCQWorkflowId 

-- Upadte existing records
UPDATE 
	dbo.ReAPDocument 
SET 
	DocumentStatusID = @IllustrationStatus 
FROM 
	ReAPDocument 
INNER JOIN 
	SingleCompanyQuote 
	ON ReAPDocument.SCQWorkflowId = SingleCompanyQuote.SCQWorkflowId 
INNER JOIN
	@UrlTable AS Temp
	ON ReAPDocument.IllustrationURL = Temp.Url
WHERE 
	SingleCompanyQuote.SCQWorkflowId = @SCQWorkflowId 

-- Add new records
INSERT 
INTO 
	dbo.ReAPDocument
	( 
		SCQWorkflowID, 
		IllustrationURL, 
		DocumentStatusID 
	) 
SELECT			
		@QuoteAndPrintStatusID, 
		Temp.Url, 
		@IllustrationStatus 
FROM 
	ReAPDocument 
INNER JOIN 
	SingleCompanyQuote 
	ON ReAPDocument.SCQWorkflowId = SingleCompanyQuote.SCQWorkflowId 
	AND SingleCompanyQuote.SCQWorkflowId = @SCQWorkflowId 
LEFT OUTER JOIN
	@UrlTable AS Temp
	ON ReAPDocument.IllustrationURL = Temp.Url
WHERE 
	SingleCompanyQuote.SCQWorkflowId IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources