What are the challenges you face when working across database platforms? Take the survey
Options

Valid Sql merge statement fails Sql Prompt layout

The below valid sql stored proc fails the layout command in Sql Prompt. Why?

<bbcode>
create PROCEDURE dbo.usp_mrg_JournyxTimeRecs
AS
BEGIN

SET NOCOUNT ON;

MERGE INTO dbo.journyx_time_recs T
USING (SELECT * FROM [[journyx].[dbo].[time_recs] WHERE LTRIM(RTRIM(record_date)) >= '20160101') S
INNER JOIN [journyx].[dbo].[projects] P ON S.id_project = P.id_project
INNER JOIN [journyx].[dbo].[users] U ON S.id_user = U.id_user
INNER JOIN [journyx].[dbo].[users] C ON S.creator = C.id_user
INNER JOIN [journyx].[dbo].[users] M ON S.modifier = M.id_user
ON T.id_time_rec COLLATE dataBase_default = S.id_time_rec COLLATE DATABASE_DEFAULT
WHEN MATCHED THEN UPDATE SET
T.time_amount = S.time_amount,
T.record_date = CONVERT(DATETIME, S.record_date),
T.company_name = dbo.usf_ProjectSplit(P.PName, '-', 1),
T.project_name = dbo.usf_ProjectSplit(P.PName, '-', 2),
T.comment = S.comment
WHEN NOT MATCHED
THEN INSERT
(
id_time_rec
, company_name
, project_name
, project_hidden
, user_login
, record_date
, id_code_task
, time_amount
, comment
, creator
, create_timestamp
, modifier
, modify_timestamp
, Active
)
VALUES
(
S.id_time_rec
, dbo.usf_ProjectSplit(P.PName, '-', 1)
, dbo.usf_ProjectSplit(P.PName, '-', 2)
, P.is_hidden
, U.user_login
, S.record_date
, S.id_code_task
, S.time_amount
, S.comment
, C.fullname
, dateadd(S, S.create_timestamp, '1970-01-01')
, M.fullname
, DATEADD(S, s.modify_timestamp, '1970-01-01')
, 1
);

END;
GO
</bbcode>
Tagged:

Answers

Sign In or Register to comment.