Wednesday, March 21, 2012

replication autogenerated procs - interesting ;2 notation

I was looking to modify how the INSERT happens with regards to replication only to find my solution in the proc itself. When I edit the proc this is what I am displayed in SQL QA or EM:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER procedure "sp_MSins_dboMEETING" @.c1 int,@.c2 int,@.c3 int,@.c4 varchar(250),@.c5 datetime,@.c6 datetime,@.c7 bit,@.c8 char(1),@.c9 datetime,@.c10 bit,@.c11 bit,@.c12 bit,@.c13 datetime,@.c14 smallint,@.c15 datetime,@.c16 smallint,@.c17 binary(8),@.c18 bit,@.c19 bit,@.c20 bit,@.c21 varchar(1000)

AS
BEGIN

insert into "dbo"."MEETING"(
"MEETING_ID", "MEETING_TYPE_ID", "MEETING_STATUS_ID", "TITLE", "START_DATE", "END_DATE", "PUBLISH_IND", "GROUP_IND", "PUBLISH_DATE", "MY_ADVISORS", "SUBMITTED_IND", "ACTIVE_IND", "CREATE_DATE", "CREATED_BY", "LAST_UPDATE_DATE", "LAST_UPDATED_BY", "DATE_INDEXED", "ON_DEMAND_IND", "NOT_REPORTED_IND", "MAJOR_PROJECT_IND", "MAJOR_PROJECT_COMMENT"
)

values (
@.c1, @.c2, @.c3, @.c4, @.c5, @.c6, @.c7, @.c8, @.c9, @.c10, @.c11, @.c12, @.c13, @.c14, @.c15, @.c16, @.c17, @.c18, @.c19, @.c20, @.c21
)

END

GO

create procedure "sp_MSins_dboMEETING";2 @.c1 int,@.c2 int,@.c3 int,@.c4 varchar(250),@.c5 datetime,@.c6 datetime,@.c7 bit,@.c8 char(1),@.c9 datetime,@.c10 bit,@.c11 bit,@.c12 bit,@.c13 datetime,@.c14 smallint,@.c15 datetime,@.c16 smallint,@.c17 binary(8),@.c18 bit,@.c19 bit,@.c20 bit,@.c21 varchar(1000)
as
if exists ( select * from "dbo"."MEETING"
where "MEETING_ID" = @.c1
)
begin
update "dbo"."MEETING" set "MEETING_TYPE_ID" = @.c2,"MEETING_STATUS_ID" = @.c3,"TITLE" = @.c4,"START_DATE" = @.c5,"END_DATE" = @.c6,"PUBLISH_IND" = @.c7,"GROUP_IND" = @.c8,"PUBLISH_DATE" = @.c9,"MY_ADVISORS" = @.c10,"SUBMITTED_IND" = @.c11,"ACTIVE_IND" = @.c12,"CREATE_DATE" = @.c13,"CREATED_BY" = @.c14,"LAST_UPDATE_DATE" = @.c15,"LAST_UPDATED_BY" = @.c16,"DATE_INDEXED" = @.c17,"ON_DEMAND_IND" = @.c18,"NOT_REPORTED_IND" = @.c19,"MAJOR_PROJECT_IND" = @.c20,"MAJOR_PROJECT_COMMENT" = @.c21
where "MEETING_ID" = @.c1
end
else
begin
insert into "dbo"."MEETING" ( "MEETING_ID","MEETING_TYPE_ID","MEETING_STATUS_ID","TITLE","START_DATE","END_DATE","PUBLISH_IND","GROUP_IND","PUBLISH_DATE","MY_ADVISORS","SUBMITTED_IND","ACTIVE_IND","CREATE_DATE","CREATED_BY","LAST_UPDATE_DATE","LAST_UPDATED_BY","DATE_INDEXED","ON_DEMAND_IND","NOT_REPORTED_IND","MAJOR_PROJECT_IND","MAJOR_PROJECT_COMMENT" ) values ( @.c1,@.c2,@.c3,@.c4,@.c5,@.c6,@.c7,@.c8,@.c9,@.c10,@.c11,@.c12 ,@.c13,@.c14,@.c15,@.c16,@.c17,@.c18,@.c19,@.c20,@.c21 )
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Is the second version listed at the bottom like a comment or can it actually get called? I am going to script out all of these procs and save them, and then remove the first version (listed at the top) and the use the second version since it does what I need. I just thought it was interesting to see two stored procedures in a single definition, never seen the "PROC_NAME";2 notation, have you? If so please tell me what it does, is it just a way to create a second version of the procedure in a comment type fashion or is it used another way?From Books online's Create Procedure reference;number
Is an optional integer used to group procedures of the same name so they can be
dropped together with a single DROP PROCEDURE statement. For example, the
procedures used with an application called orders may be named orderproc;1,
orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the
entire group. If the name contains delimited identifiers, the number should
not be included as part of the identifier; use the appropriate delimiter around
procedure_name only.
I'll admit that I have neither used this functionality, nor ever seen it used. It may be that the application specifies which version of the procedure is executed by including the number in the call. And in that case, I wouldn't go dropping or commenting out the code, since it could break your app.

No comments:

Post a Comment