USE [crm2]
GO
/****** Object: StoredProcedure [dbo].[GetBillsID] Script Date: 09/22/2018 15:13:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: SQLQuery1.sql|0|0|C:UsersadminAppDataLocalTemp~vsC414.sql
ALTER PROCEDURE [dbo].[GetBillsID]
(@Billsmode INT=0,
@BillsType VARCHAR(30),
@DateType varchar(30),
@GetId int
)
AS
BEGIN
-- routine body goes here,e.g.
-- SELECT ‘Navicat for SQL Server‘
if (@billsmode=0)
BEGIN
if (exists(select ( BillsID.DateCount) from BillsID where [email?protected] and [email?protected]))
BEGIN
select @GetId=BillsID.DateCount from BillsID where [email?protected] and [email?protected]
set @[email?protected]+1
select @BillsType+‘-‘+CONVERT(varchar(100),@DateType,23)+‘-‘+replace(right(str(@GetId),4),‘ ‘,‘0‘)
update dbo.BillsID with (TABLOCKX) set [email?protected] where ([email?protected] and [email?protected])
end
else
begin
set @[email?protected]+1
select @BillsType+‘-‘+CONVERT(varchar(100),‘0‘)
INSERT INTO dbo.BillsID with (TABLOCKX) (BillsType,DateType,DateCount) VALUES (@BillsType,@GetId)
end
END
if (@billsmode=1)
BEGIN
if (exists(select ( BillsID.MonthCount) from BillsID where [email?protected] and MonthType=left(convert(varchar,21),7)))
BEGIN
select @GetId=BillsID.MonthCount from BillsID where [email?protected] and MonthType=left(convert(varchar,7)
set @[email?protected]+1
select @BillsType+‘-‘+left(convert(varchar,7)+‘-‘+replace(right(str(@GetId),6),‘0‘)
update dbo.BillsID with (TABLOCKX) set [email?protected] where ([email?protected] and MonthType=left(convert(varchar,7))
end
else
begin
set @[email?protected]+1
select @BillsType+‘-‘+left(convert(varchar,MonthType,MonthCount) VALUES (@BillsType,left(convert(varchar,7),@GetId)
end
end
if (@billsmode=2)
BEGIN
if (exists(select ( BillsID.YearCount) from BillsID where [email?protected] and YearType=left(convert(varchar,4)))
BEGIN
select @GetId=BillsID.YearCount from BillsID where [email?protected] and YearType=left(convert(varchar,4)
set @[email?protected]+1
select @BillsType+‘-‘+left(convert(varchar,4)+‘-‘+replace(right(str(@GetId),8),‘0‘)
update dbo.BillsID with (TABLOCKX) set [email?protected] where ([email?protected] and YearType=left(convert(varchar,4))
end
else
begin
set @[email?protected]+1 select @BillsType+‘-‘+left(convert(varchar,‘0‘) INSERT INTO dbo.BillsID with (TABLOCKX) (BillsType,YearType,YearCount) VALUES (@BillsType,@GetId) end endEND
