[dba-SQLServer] Formatting A Number Into A Three Digit Character String

paul.hartland at fsmail.net paul.hartland at fsmail.net
Wed Jun 29 02:26:20 CDT 2005


To all,

I have the foloowing stored procedure :

CREATE PROCEDURE [genesis_insert_ResourceGuide] 
 (
  @strClient  [nvarchar](255),
  @strJobType  [nvarchar](100),
  @strACNo  [nvarchar](50),
  @strLevel  [nvarchar](75),
  @intMin   [int],
  @intMax  [int]
        )
AS
SET NOCOUNT ON
WHILE    @intMin  <= @intMax
BEGIN
INSERT INTO   tblResourceGuide
  (
   [Name],
   [ClientJobType],
   [A/CNo],
   [CountLevel],
   [Items],
   [Value],
   [Manager],
   [Supervisor],
   [Control],
   [Checker],
   [Counter],
   [Planner],
   [Dispensary],
   [Pharmacy]
     )
VALUES
  (
   @strClient,
   @strJobType,
   @strACNo,
   'Level ' + CONVERT(nvarchar, at intMin),
   0,
   0,
   0,
   0,
   0,
   0,
   0,
   0,
   0,
   0
     )
SET   @intMin  = @intMin  + 1
IF    @intMin  <= @intMax
 CONTINUE
ELSE
 BREAK
END
SET NOCOUNT OFF
GO

This line 'Level ' + CONVERT(nvarchar, at intMin) as you will see converts a number into a string and adds it to Level (i.e Level 1, Level 2 etc etc).....The maximum a level will ever be is 999, so what I would like instead of Level 1, Level 2 etc etc is Level 001, Level 002 etc etc.  Can someone tell me the function or code that I can use to achieve this.

Thanks in advance for any help on this...

Paul Hartland
Database Developer

-- 

Whatever you Wanadoo:
http://www.wanadoo.co.uk/time/

This email has been checked for most known viruses - find out more at: http://www.wanadoo.co.uk/help/id/7098.htm


More information about the dba-SQLServer mailing list