January 18, 2011 at 10:15 am
You appear to selecting not merging.
Try something like:
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.GetTblGUID
@Address nvarchar(255)
,@GUID uniqueidentifier OUTPUT
AS
DECLARE @GUIDs TABLE
(
[GUID] uniqueidentifier NOT NULL
);
INSERT INTO Tbl
OUTPUT inserted.[GUID] INTO @GUIDs
SELECT NEWID(), @Address
WHERE NOT EXISTS
(
SELECT *
FROM Tbl WITH (UPDLOCK, SERIALIZABLE)
WHERE [Address] = @Address
);
IF @@ROWCOUNT = 0
SET @GUID = (SELECT [GUID] FROM Tbl WHERE [Address] = @Address);
ELSE
SET @GUID = (SELECT [GUID] FROM @GUIDs);
GO
-- eg of use
DECLARE @Guid uniqueidentifier
EXEC dbo.GetTblGUID 'Some Address', @Guid OUTPUT
SELECT @Guid
January 18, 2011 at 11:41 pm
Thanks for the example but i am looking to do the same using MERGE
not sure that it is possible but was wondering if it is possible somehow
Dani Avni
OfficeCore
January 19, 2011 at 5:15 am
Your update does not appear to be doing any updating so MERGE might not be the best way to do this.
Anyway you can add an OUTPUT clause to the merge to return the fields.
MERGE Tbl AS [target]
USING (SELECT @ADDRESS) AS [source] ([Address])
ON ([target].[Address] = [source].[Address])
WHEN MATCHED THEN
UPDATE SET @GUID = [target].GUID
WHEN NOT MATCHED THEN
INSERT ([GUID],[Address])
VALUES (newid(),@ADDRESS)
OUTPUT INSERTED.[Address], INSERTED.GUID;
January 19, 2011 at 10:17 pm
create procedure usp_Returnadd(@Address varchar(max))as
begin
insert into tbl
select newid(),@Address
WHERE not EXISTS
(
SELECT * FROM Tbl WHERE [Address] = @Address
);
select guid from tbl where address=@Address
end
--exec usp_Returnadd 'sample address'
January 19, 2011 at 11:57 pm
thanks for the posts. as i said i know how to do it without merge. i was looking for the merge way to do it
Dani Avni
OfficeCore
Viewing 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply