반응형
CREATE PROCEDURE [dbo].[AP_getcustcode] AS
BEGIN
RETURN SELECT dbo.lpad(
( select max(cust_code)+1 from customerinfo ), 15, '0' )
==============================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[GetCSContactSeqNew] ( @CustCode char(15) )
RETURNS char(3) AS
BEGIN
declare @id char(3)
SELECT @id = dbo.LPAD( ISNULL(MAX(CSCON_CODE)+1,1), 3, '0') FROM CSCONTACT WHERE CUST_CODE = @CustCode
IF @id = NULL
BEGIN
SELECT @id = dbo.LPAD( '1', 3, '0')
END
RETURN @id
END
dbo.GetCSContactSeqNew('${CUST_CODE}')
==============================================
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[LPAD]
( @sSrcValue nvarchar(20), @nSize int, @sChar nvarchar(2))
RETURNS nvarchar(100) AS
BEGIN
RETURN (ISNULL( REPLICATE(@sChar, @nSize-len(@sSrcValue))+@sSrcValue, substring(@sSrcValue,len(@sSrcValue)-@nSize+1,len(@sSrcValue) ) ))
END
==============================================
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[RPAD]
( @sSrcValue nvarchar(20), @nSize int, @sChar nvarchar(2))
RETURNS nvarchar(100) AS
BEGIN
RETURN (@sSrcValue+REPLICATE(@sChar, @nSize-len(@sSrcValue)))
END
==============================================
반응형
'Database' 카테고리의 다른 글
[ORACLE]Table Lock 걸렸을 때 처리 (ORA-00054) (0) | 2012.04.19 |
---|---|
[ORACLE]테이블명,인덱스명 변경 (0) | 2012.03.30 |
쿼리 깔끔하게 정리하기 (0) | 2012.03.14 |
[ORACLE]테이블의 컬럼을 변경한 유저를 아는 방법 (0) | 2012.03.14 |
[ORACLE]다른 DB User 에게 자신의 테이블을 볼수 있도록 select 권한주기 (0) | 2012.03.13 |