Need help with MERGE statement.

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 2008 Forums

Transact-SQL (2008)

Need help with MERGE statement.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-10-27 : 12:10:08

Hi,

Is there a way to return the last CustId when INSERT but NOT in UPDATE. I need to return this value back to calling program.

I can work around this using EXISTS but I wonder if this can be done using Merge statement. SQL2008

Your help is greatly appreciate.

IF OBJECT_ID('uspCustAcctTypeInsUpt', 'p') IS NOT NULL

DROP PROCedure uspCustAcctTypeInsUpt

GO

CREATE PROCedure uspCustAcctTypeInsUpt

(

@CustId INT = NULL,

@CustAcctType VARCHAR(15) = NULL,

@CustDesc VARCHAR(100) = NULL

)

AS

/***********************************************************************************************

** Modifications:

** ----------------------------------

** Date: Author: Reasons:

** ------------+-----------------------------------------------------------------------

**

**

***********************************************************************************************/

SET nocount ON

MERGE CustAcctType AS a

USING ( SELECT @CustId AS CustId, @CustAcctType AS CustAcctType, @CustDesc AS CustDesc ) AS b

ON a.CustId = b.CustId

WHEN MATCHED THEN

UPDATE

SET a.CustAcctType = b.CustAcctType,

a.CustDesc = b.CustDesc

WHEN NOT MATCHED

THEN INSERT VALUES ( @CustAcctType, @CustDesc);

--Not working yet...

IF (@CustId IS NULL)

BEGIN

SELECT CustId

FROM CustAcctType

WHERE CustId = SCOPE_IDENTITY()

END

GO

/*

--Testing...

IF OBJECT_ID('CustAcctType', 'u') IS NOT NULL

DROP TABLE CustAcctType

GO

CREATE TABLE CustAcctType

(

CustId INT IDENTITY(1,1) NOT NULL,

CustAcctType VARCHAR(15) NULL,

CustDesc VARCHAR(100) NULL

)

go

Ex:

When I run the sp below it should return 1 but when I run update it should not return 1.

EXECute uspCustAcctTypeInsUpt @CustId = 1,

@CustAcctType = 'NewCust1',

@CustDesc = 'This is a test1';

GO

-- TRUNCATE TABLE CustAcctType

SELECT *

FROM CustAcctType;

GO

*/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-27 : 14:28:25

Humm, It seems to be working for me. Can you verify that you are passing @CustID as an actual NULL value?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-27 : 14:39:14

in your exec statement you are passing @CustID as 1 which is not NULL so SELECt wont get executed at all inside IF and you want get any value returned as output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-10-27 : 14:53:37

[code]CREATE PROCEDURE dbo.uspCustAcctTypeInsUpt
(
@CustID INT OUTPUT = NULL,
@CustAcctType VARCHAR(15) = NULL,
@CustDesc VARCHAR(100) = NULL
)
AS

SET NOCOUNT ON

DECLARE @ID TABLE
(
CustID INT
)

MERGE dbo.CustAcctType AS tgt
USING (
SELECT @CustId AS CustID,
@CustAcctType AS CustAcctType,
@CustDesc AS CustDesc
) AS src ON src.CustID = tgt.CustID
WHEN MATCHED
THEN UPDATE
SET tgt.CustAcctType = src.CustAcctType,
tgt.CustDesc = src.CustDesc
WHEN NOT MATCHED BY TARGET
THEN INSERT (
CustAcctType,
CustDesc
)
VALUES (
src.CustAcctType,
src.CustDesc
)
OUTPUT inserted.CustID
INTO @ID
(
CustID
);

SELECT @CustID = CustID
FROM @ID[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources