SELECT	LoanNum = dbo.fnParseString(-3, '/', ltrim(Data)), URL = ltrim(Data)
FROM	dbo.fnParseList(';', @URL)

Hi,

The SP below is working but I wonder If there is a better way to write this Without using the #Temp table and
get the results showing with 4 test cases below.  I wonder if you can help to incorporate into MERGE statement
and get the same results.


Thanks in advance.


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DocumentAttachment]') AND type in (N'U'))
DROP TABLE [dbo].[DocumentAttachment]
GO

CREATE TABLE [dbo].[DocumentAttachment]
(
	[DocumentActionId] [int] NOT NULL,
	[LoanNum] [varchar](10) NULL,
	[InsertDt] [datetime] NOT NULL,
	[InsertBy] [varchar](50) NOT NULL,
	[URL] [varchar](8000) NULL
) 
GO

 INSERT DocumentAttachment (DocumentActionId, LoanNum, InsertDt, InsertBy, URL)
 VALUES (1, '10001', '2011-02-08 15:36:06.937', 'acruz', '/Doc/10001/CC/Test/5532/02/08/2011');
  GO


--Testing...
EXECute dbo.spWorkQ_DocumentAttachmentUpdate @DocumentActionId = 1
                                                ,@LoginId  = 'Users'
                                                ,@URL = '/Doc/10001/CC/Test/5532/02/08/2011abcd;/Doc/10001/CC/Test/5532/02/08/2011 ZYZ'
    GO
    
     SELECT *
       FROM DocumentAttachment;
     GO    
     
--Case #1.
-- Nothing has changed.  @URL = '/Doc/10001/CC/Test/5532/02/08/2011;/Doc/10001/CC/Test/5532/02/08/2011'

-- Result want:

DocumentActionId LoanNum    InsertDt                InsertBy                                           URL
---------------- ---------- ----------------------- -------------------------------------------------- ----------------------------------
1                10001      2011-02-08 15:36:06.937 acruz                                              /Doc/10001/CC/Test/5532/02/08/2011   

--Case #2.  There is a changed in value ZYZ, insert a new row. @URL = '/Doc/10001/CC/Test/5532/02/08/2011;/Doc/10001/CC/Test/5532/02/08/2011 ZYZ'
DocumentActionId LoanNum    InsertDt                InsertBy                                           URL
---------------- ---------- ----------------------- -------------------------------------------------- --------------------------------------
1                10001      2011-02-08 15:36:06.937 acruz                                              /Doc/10001/CC/Test/5532/02/08/2011
1                10001      2011-02-10 08:47:07.760 Users                                              /Doc/10001/CC/Test/5532/02/08/2011 ZYZ -- Insert a new row.

--Case #3.  There is a changed in values and delete the old one in the table /Doc/10001/CC/Test/5532/02/08/2011.
-- @URL = '/Doc/10001/CC/Test/5532/02/08/2011abc;/Doc/10001/CC/Test/5532/02/08/2011 ZYZ'

DocumentActionId LoanNum    InsertDt                InsertBy                                           URL
---------------- ---------- ----------------------- -------------------------------------------------- ---------------------------------------
1                10001      2011-02-10 08:47:07.760 Users                                              /Doc/10001/CC/Test/5532/02/08/2011 ZYZ
1                10001      2011-02-10 08:48:51.150 Users                                              /Doc/10001/CC/Test/5532/02/08/2011abcd


--Case #4.  Delete all the values if URL passed in blank @URL = ''
-- @URL = ''
DocumentActionId LoanNum    InsertDt                InsertBy                                           URL
---------------- ---------- ----------------------- -------------------------------------------------- ---------------------------------------




IF OBJECT_ID('[dbo].[spWorkQ_DocumentAttachmentUpdate]', 'p') IS NOT NULL
  DROP PROCedure [dbo].[spWorkQ_DocumentAttachmentUpdate]
GO

CREATE PROCedure [dbo].[spWorkQ_DocumentAttachmentUpdate]
(
  @DocumentActionId    INT
 ,@LoginId             VARCHAR(50)
 ,@URL                 VARCHAR(8000)
)
AS
/**********************************************************************************************
**     Modifications:
**     ----------------------------------
**     Date:         Author:     Reasons:
**     ------------+-----------------------------------------------------------------------
**
**
*********************************************************************************************/
SET nocount ON

DECLARE @ErrorMessage   VARCHAR(4000),
        @ErrorSeverity  INT,
        @ErrorState     INT,
        @LoanNum        VARCHAR(10)


       SELECT @LoanNum = LoanNum
         FROM DocumentAttachment
        WHERE DocumentActionId = @DocumentActionId
        
        
IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL
  DROP TABLE #Temp 

CREATE TABLE #Temp 
(
  DocumentActionId    INT           NULL,
  LoanNum             VARCHAR(10)   NULL,
  insertDt            DATETIME      NULL,
  InsertBy            VARCHAR(50)   NULL,
  URL                 VARCHAR(8000) NULL
 )
 
 
   INSERT #Temp (DocumentActionId, LoanNum, InsertDt, InsertBy, URL)
   SELECT DISTINCT @DocumentActionId AS 'DocumentActionId',
                   @LoanNum AS LoanNum,
                   GETDATE() AS 'InsertDt',
                   @LoginID AS InsertBy, 
                   item AS 'URL'
    FROM dbo.fn_tblDelimiter(@URL, ';') 
   
    --SELECT * FROM #Temp
    
 
BEGIN TRY
      

   MERGE INTO DocumentAttachment AS a
   USING #Temp AS b
      ON a.DocumentActionId = b.DocumentActionId
     AND a.LoanNum = b.LoanNum
     AND a.URL = b.URL
    WHEN MATCHED AND (a.URL <> b.URL) THEN 
       UPDATE
          SET URL      = b.URL,
              InsertDt = GETDATE(),
              InsertBy = b.InsertBy
  WHEN NOT MATCHED BY TARGET
  THEN INSERT (DocumentActionId, LoanNum, InsertBy, InsertDt, URL)
  VALUES      (b.DocumentActionId, b.LoanNum, b.InsertBy, B.InsertDt, b.URL);
  
  
   DELETE a 
     FROM DocumentAttachment AS a
    WHERE NOT EXISTS (SELECT 1 
                        FROM #Temp AS b
                       WHERE a.DocumentActionId = b.DocumentActionId
                         AND a.LoanNum = b.LoanNum
                         AND a.url = b.URL)
      AND a.DocumentActionId = @DocumentActionId
      AND a.LoanNum = @LoanNum

END TRY



BEGIN CATCH

  SELECT @ErrorMessage  = ERROR_MESSAGE(),
         @ErrorSeverity = ERROR_SEVERITY(),
         @ErrorState    = ERROR_STATE()

  RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)

END CATCH
SET nocount OFF
GO

-------------------------------------------------------------------

-- create a function.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_tblDelimiter]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_tblDelimiter]
GO
CREATE FUNCTION [dbo].[fn_tblDelimiter] (@list varchar(8000),@Delimeter char(1))
RETURNS @tblItem TABLE(Item varchar(50))
AS
BEGIN

DECLARE @Item varchar(50)
DECLARE @StartPos int, @Length int
WHILE LEN(@list) > 0
  BEGIN
    SET @StartPos = CHARINDEX(@Delimeter, @list)
    IF @StartPos < 0 SET @StartPos = 0
    SET @Length = LEN(@list) - @StartPos - 1
    IF @Length < 0 SET @Length = 0
    IF @StartPos > 0
      BEGIN
        SET @Item = SUBSTRING(@list, 1, @StartPos - 1)
        SET @list = SUBSTRING(@list, @StartPos + 1, LEN(@list) - @StartPos)
      END
    ELSE
      BEGIN
        SET @Item = @list
        SET @list = ''
      END
    INSERT @tblItem (Item) VALUES(@Item)
END

return
END
GO

quote:

---

Originally posted by khtan

you can use fnParseList() to split the @URL into rows and possibly fnParseString() to extract the LoanNum 10001, 10002 from the URL
both function can be obtained from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

something like this . .

SELECT	LoanNum = dbo.fnParseString(-3, '/', ltrim(Data)), URL = ltrim(Data)
FROM	dbo.fnParseList(';', @URL)

---

KH
[spoiler]Time is always against us[/spoiler]

---