January 18, 2019 at 11:20 pm
Hi every body
I use numbered_procedures because of versioning purpose. (Notice: versioning means version of live and up procedures and Doesn't mean source control purpose)
But unfortunately Numbered procedures are deprecated (SQL 2017).
Please help me what can i do now?
January 23, 2019 at 9:29 am
I don't understand what you mean by "versioning purpose". Perhaps others don't either, which may be the reason that no one else has responded to your question. Please explain further. What do you mean when you say "version of live and up procedures"? Examples may help me understand; please give examples of procedure names and why you use the names and numbers you have chosen. More information will hopefully allow me to understand and offer suggestions.
January 23, 2019 at 10:36 am
this link describes numbered procedures fairly well; it is a little used capability that is now deprecated.
you can have different versions of the same procedure, or even completely different processes under the same procedure name.
I kind of thought of it as a "package" in oracle, where a group of procs were related, but instead they had the same name, and indexed by a number instead.
With it now being deprecated, you are stuck with a redesign or sticking with 2016 until you are ready to redesign.
As far as redesigning, what if you replaced ;2 with_2 and have a fully qualified name? how would that affect your processes?
calling execute dbo.usp_cdcproc_2 @param instead? what are you using the versioning for?
https://sqlwhisper.wordpress.com/2014/09/09/numbered-stored-procedure/
create procedure usp_cdcproc(@a int)
as
begin
Select 'This is Numbered Stored Procedure '+try_convert(varchar(1),@a)
end
go
create procedure usp_cdcproc;2(@a int)
as
begin
Select 'This is second Numbered Stored Procedure '+try_convert(varchar(2),@a)
end
go
CREATE procedure usp_cdcproc;3(@a varchar(2),@Another VARCHAR(30) = NULL)
as
begin
Select 'This is third Numbered Stored Procedure with another parmeter!'+@a
end
GO
Select name,Object_ID,type_desc from sys.procedures
DECLARE @Param INT = 42
EXECUTE usp_cdcproc;1 @Param;
EXECUTE usp_cdcproc;2 @Param;
EXECUTE usp_cdcproc;3 @Param;
Lowell
January 23, 2019 at 12:21 pm
In the past, I've used schemas to isolate different versions of the same object within the same database. I used this typically in development or for parallel testing in QA. A benefit of using schemas is that multiple objects belonging to the same release can be easily grouped.
For example:
CREATE PROCEDURE dbo.MonthEndReport ...
GO
CREATE PROCEDURE V201801.MonthEndReport ...
GO
CREATE PROCEDURE V201707.MonthEndReport ...
GO
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 25, 2019 at 1:13 pm
parviz.vakili - Friday, January 18, 2019 11:20 PMHi every bodyI use numbered_procedures because of versioning purpose. (Notice: versioning means version of live and up procedures and Doesn't mean source control purpose)
But unfortunately Numbered procedures are deprecated (SQL 2017).
Please help me what can i do now?
The numbered procedures are left over from the old Sybase days. Back when SQL Server was created, computers were rather expensive and client/server computing was brand-new. This and the idea of table owners were a clumsy trick to let multiple people share the hardware. You really should not be using it today, but you select to support it because of people like you who haven't updated their programming techniques in the past 20+ years. . One of the jokes in this trade has been "there's nothing more permanent than a temporary fix in software"
Please post DDL and follow ANSI/ISO standards when asking for help.
January 27, 2019 at 6:44 am
fahey.jonathan - Wednesday, January 23, 2019 9:29 AMI don't understand what you mean by "versioning purpose". Perhaps others don't either, which may be the reason that no one else has responded to your question. Please explain further. What do you mean when you say "version of live and up procedures"? Examples may help me understand; please give examples of procedure names and why you use the names and numbers you have chosen. More information will hopefully allow me to understand and offer suggestions.
My mean is: Customers is using that versions now.
For example, customer A use MySP ver 1 and Customer B use MySP ver 2.
previously I named that procedures Like This:
dbo.MySP_Ver01
dbo.MySP_Ver02
Now I use this convention:
dbo.MySP;1
dbo.MySP;2
Some customers use dbo.MySP;2 and anothers use dbo.MySP;1 until they migrate to dbo.MySP;2
We have 2 scenarios
1.different combination of parameters
2.different body of procedures with same input parameters (like: change calculate method and etc )
we use this scenarios in banking industry, we change some business logic base on new regulatory compliance and change our procedures without changing their names.
but some banks are selected as a pilot and test, therefore we need 2 versions of procedures.
January 27, 2019 at 6:50 am
jcelko212 32090 - Friday, January 25, 2019 1:13 PMThe numbered procedures are left over from the old Sybase days. Back when SQL Server was created, computers were rather expensive and client/server computing was brand-new. This and the idea of table owners were a clumsy trick to let multiple people share the hardware. You really should not be using it today, but you select to support it because of people like you who haven't updated their programming techniques in the past 20+ years. . One of the jokes in this trade has been "there's nothing more permanent than a temporary fix in software"
Tanks dear joe
but what is the best practice or solution for may scenario ?
January 27, 2019 at 8:08 am
parviz.vakili - Sunday, January 27, 2019 6:50 AMTanks dear joe
but what is the best practice or solution for my scenario?
Rename them one by one.
Please post DDL and follow ANSI/ISO standards when asking for help.
January 27, 2019 at 8:32 am
jcelko212 32090 - Sunday, January 27, 2019 8:08 AMRename them one by one.
thanks anyway
i found the answer by this link:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/fbfec201-f60a-4165-a281-aad861587763/what-is-alternative-of-numberedprocedures?forum=databasedesign
January 28, 2019 at 9:40 am
A wrapper is a good idea, especially if you can get a version somehow and have the procedure automatically change things.
My $0.02, Microsoft is not removing numbered procedures. They're deprecated, but they work in 2017 and 2019. You can continue to use them, but having played with them, I find no difference between
MyProc
MyProc;2
and
MyProc
MyProc_2
Either way, client code needs to change.
January 28, 2019 at 9:52 am
parviz.vakili - Sunday, January 27, 2019 6:44 AMfahey.jonathan - Wednesday, January 23, 2019 9:29 AMI don't understand what you mean by "versioning purpose". Perhaps others don't either, which may be the reason that no one else has responded to your question. Please explain further. What do you mean when you say "version of live and up procedures"? Examples may help me understand; please give examples of procedure names and why you use the names and numbers you have chosen. More information will hopefully allow me to understand and offer suggestions.My mean is: Customers is using that versions now.
For example, customer A use MySP ver 1 and Customer B use MySP ver 2.
previously I named that procedures Like This:
dbo.MySP_Ver01
dbo.MySP_Ver02
Now I use this convention:
dbo.MySP;1
dbo.MySP;2
Some customers use dbo.MySP;2 and anothers use dbo.MySP;1 until they migrate to dbo.MySP;2
We have 2 scenarios
1.different combination of parameters
2.different body of procedures with same input parameters (like: change calculate method and etc )
we use this scenarios in banking industry, we change some business logic base on new regulatory compliance and change our procedures without changing their names.
but some banks are selected as a pilot and test, therefore we need 2 versions of procedures.
Couldn't this be handled by branching on your version control system? Since application code would call the stored procedures, simply deploy the correct procedure that match the application code you're releasing.
I honestly don't see the advantage on using any form of numbered procedures in production environments.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply