Ye39rj0s

1Generating/updating unique random barcode numbers in a column in SQL
2USE [FSDBGL]
3GO
4
5
6SET ANSI_NULLS ON
7GO
8
9SET QUOTED_IDENTIFIER ON
10GO
11
12SET ANSI_PADDING ON
13GO
14
15CREATE TABLE [dbo].[Mfg_ITMMAST](
16[IMPN] [varchar](30) NOT NULL,
17[IMDESC] [varchar](70) NOT NULL,
18[IMUPCCD] [varchar](13) NOT NULL,
19) ON [PRIMARY]
20
21GO
22
23SET ANSI_PADDING OFF
24GO
25	
26USE [stackoverflow]
27GO
28SET ANSI_NULLS ON
29GO
30SET QUOTED_IDENTIFIER ON
31GO
32SET ANSI_PADDING ON
33GO
34--DROP TABLE [dbo].[Mfg_ITMMAST];
35CREATE TABLE [dbo].[Mfg_ITMMAST](
36    [IMPN] [varchar](30) NOT NULL,
37    [IMDESC] [varchar](70) NOT NULL,
38    [IMUPCCD] [varchar](13) NOT NULL
39) ON [PRIMARY]
40GO
41SET ANSI_PADDING OFF
42GO
43INSERT [dbo].[Mfg_ITMMAST] ([IMPN], [IMDESC], [IMUPCCD]) VALUES (N'40000-01', N'test', N'601040')
44INSERT [dbo].[Mfg_ITMMAST] ([IMPN], [IMDESC], [IMUPCCD]) VALUES (N'41023-01', N'test', N'601040123456')
45INSERT [dbo].[Mfg_ITMMAST] ([IMPN], [IMDESC], [IMUPCCD]) VALUES (N'41001-02', N'test', N'601040')
46INSERT [dbo].[Mfg_ITMMAST] ([IMPN], [IMDESC], [IMUPCCD]) VALUES (N'51001-01', N'test', N'601040')
47INSERT [dbo].[Mfg_ITMMAST] ([IMPN], [IMDESC], [IMUPCCD]) VALUES (N'51001-02', N'test', N'601040')
48INSERT [dbo].[Mfg_ITMMAST] ([IMPN], [IMDESC], [IMUPCCD]) VALUES (N'51014-02', N'test', N'601040234567')
49INSERT [dbo].[Mfg_ITMMAST] ([IMPN], [IMDESC], [IMUPCCD]) VALUES (N'61001-01', N'test', N'601040')
50	
51CREATE PROCEDURE uspScanForBlankUpcs
52AS
53
54  -- setup variables for bringing in blank row data
55  DECLARE @IMPN [varchar](30), @IMUPCCD [varchar](13),
56          @blankUpc [varchar](13), @upcPrefix [varchar](6),
57          @random [varchar](6), @retryRandom bit;
58  SET @blankUpc = '601040'; -- This is the value of IMUPCCD when it is "blank"
59  SET @upcPrefix = '601040'; -- This is prefix for our randomly generated UPC
60
61  -- setup the cursor, query for items with "blank" UPCs
62  DECLARE blanksCursor CURSOR FOR
63  SELECT IMPN
64  FROM [Mfg_ITMMAST]
65  WHERE (LEFT(IMPN, 5) >= '40000' AND
66         LEFT(IMPN, 5) < '60000' AND
67         RIGHT(IMPN, 2) IN ('01','02')) AND
68        IMUPCCD = @blankUpc
69  ;
70
71  -- open the cursor
72  OPEN blanksCursor;
73
74  -- load the next row from the cursor
75  FETCH NEXT FROM blanksCursor
76  INTO @IMPN;
77
78  -- loop through each row of the cursor
79  WHILE @@FETCH_STATUS = 0
80  BEGIN
81    --PRINT 'IMPN: ' + @IMPN;
82    -- try to create a new random number
83    SET @retryRandom = 1;
84    WHILE @retryRandom = 1
85    BEGIN
86      -- get a random number for the UPC, then left-pad it with zeros to 6 digits
87      SET @random = RIGHT('00000' + CONVERT(VARCHAR, FLOOR(RAND() * 999999)), 6);
88      -- concatenate the UPC prefix with the random number
89      SET @IMUPCCD = @upcPrefix + @random
90      --PRINT 'IMUPCCD: ' + @IMUPCCD;
91      -- see if this UPC already exists on another item
92      IF (SELECT COUNT(*) FROM [Mfg_ITMMAST] WHERE [IMUPCCD] = @IMUPCCD) > 0
93        SET @retryRandom = 1; -- UPC already existed (collision) try again
94      ELSE
95        SET @retryRandom = 0; -- didn't already exist, so exit out of loop
96    END
97
98    --PRINT 'Updating...';
99    -- Update the UPC with the random number
100    UPDATE [Mfg_ITMMAST]
101    SET IMUPCCD = @IMUPCCD
102    WHERE IMPN = @IMPN
103    ;
104
105    -- Load the next result
106    FETCH NEXT FROM blanksCursor
107    INTO @IMPN;
108
109  END
110  CLOSE blanksCursor;
111  DEALLOCATE blanksCursor;
112
113GO
114	
115exec uspScanForBlankUpcs;