Hi there.
I'm brand new to writing stored procedures, so please forgive if I appear ignorant.
I built a workflow process to add items to my database using a create reference and get next to have it compare for duplicates and then add non-duplicate items. Althought his works well, it's kind of slow.
So, I did more research and learned that the SQL command "merge" will do what I want at a data level much much much much quicker.
So, I am attempting to write a stored procedure that I can use in my workflow to accomplish this. What I wrote I have pasted below.
I am now trying to create a smart object to execute the stored procedure, but it doesn't create any properties and when I drag the execute method over to the methods tab, it won't let me map anything nor hit "next" or "finish" thus I can't complete the smart object.
What I was thinking would happen is that it would just allow me to execute the procedure via the smart object...that's all I want it to do. There are no variables or anything like that...everything is already stored in the tables for the data I want to manipulate.
What am I missing? I am not finding a lot of great info in the K2 documentation on this.
Rob
USE 0ProductionControl]
GO
/****** Object: StoredProcedure cdbo].oMergeCREST] Script Date: 1/31/2017 7:19:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Nicholas, Robert>
-- Create date: <27-Jan-2017>
-- Description: <Merge ASO Items into PROD Database Table>
-- =============================================
ALTER PROCEDURE ;dbo].rMergeCREST]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
merge work_items wi
using loader_staging cs
on wi.reference = cs.reference
when not matched then
insert
(reference, type, group_id, sub_group_id, crest_proposed_eff_date, record_date_whenload, report_or_entry_date, target_date, unitid, work_source, current_status, ep_aso_current_stat_date
, created_by, created_date)
values
(cs.reference, cs.type, cs.ogroup], cs.subgroup, cs.oeff date], getdate(), date, getdate(), cs.unit, 25, 1, getdate(), 'Uploader System', getdate());
END
