[MSSQL]LPAD,RPAD,프로시저

Database 2012.03.20 11:49 Posted by 애플자라

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

==============================================


댓글을 달아 주세요