How can we call sql function/sp in merge statement

Question

How can we call sql function/sp in merge statement

Arjun

SSC Enthusiast

Points: 185
More actions
December 28, 2019 at 9:13 pm

#3710965

Hi,
we have merger sp with table-type now we have to call one sql function to populate one column value.
CREATE PROCEDURE [dbo].[usp_upsert_Product] @Product [DataTypeforProduct] READONLY
AS
BEGIN
SET NOCOUNT ON
MERGE ProductMST AS target
USING @Product AS source
ON (target.ID=source.ID)
WHEN MATCHED THEN
UPDATE SET
ProcductName =SOURCE.ProductName,
RecordMOdifiedDate=Getdate()
WHEN NOT MATCHED THEN
INSERT (
ProcductName,
ProductCode
)
VALUES(
SOURCE.ProductName,
---We have sql funcation to get productcode from diffrent tables.--
ProductCodeFuncation(Source.ProductType));
end

Can we call ProductCodeFuncation(Source.ProductType) function in merge sp ?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 17601 More actions · Answer 1

<table_source> ::=
{  
    table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ]
        [ WITH ( table_hint [ [ , ]...n ] ) ]
  | rowset_function [ [ AS ] table_alias ]
        [ ( bulk_column_alias [ ,...n ] ) ]
  | user_defined_function [ [ AS ] table_alias ]  
  | OPENXML <openxml_clause>
  | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
  | <joined_table>
  | <pivoted_table>
  | <unpivoted_table>
}

https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15

But you will get better performance if you change the MERGE to an UPDATE followed by an INSERT with a "where not exists".