· 9 years ago · Sep 30, 2016, 04:00 AM
1USE [FSDBGL]
2GO
3
4
5SET ANSI_NULLS ON
6GO
7
8SET QUOTED_IDENTIFIER ON
9GO
10
11SET ANSI_PADDING ON
12GO
13
14CREATE TABLE [dbo].[Mfg_ITMMAST](
15[IMPN] [varchar](30) NOT NULL,
16[IMDESC] [varchar](70) NOT NULL,
17[IMUPCCD] [varchar](13) NOT NULL,
18) ON [PRIMARY]
19
20GO
21
22SET ANSI_PADDING OFF
23GO
24
25USE [stackoverflow]
26GO
27SET ANSI_NULLS ON
28GO
29SET QUOTED_IDENTIFIER ON
30GO
31SET ANSI_PADDING ON
32GO
33--DROP TABLE [dbo].[Mfg_ITMMAST];
34CREATE TABLE [dbo].[Mfg_ITMMAST](
35 [IMPN] [varchar](30) NOT NULL,
36 [IMDESC] [varchar](70) NOT NULL,
37 [IMUPCCD] [varchar](13) NOT NULL
38) ON [PRIMARY]
39GO
40SET ANSI_PADDING OFF
41GO
42INSERT [dbo].[Mfg_ITMMAST] ([IMPN], [IMDESC], [IMUPCCD]) VALUES (N'40000-01', N'test', N'601040')
43INSERT [dbo].[Mfg_ITMMAST] ([IMPN], [IMDESC], [IMUPCCD]) VALUES (N'41023-01', N'test', N'601040123456')
44INSERT [dbo].[Mfg_ITMMAST] ([IMPN], [IMDESC], [IMUPCCD]) VALUES (N'41001-02', N'test', N'601040')
45INSERT [dbo].[Mfg_ITMMAST] ([IMPN], [IMDESC], [IMUPCCD]) VALUES (N'51001-01', N'test', N'601040')
46INSERT [dbo].[Mfg_ITMMAST] ([IMPN], [IMDESC], [IMUPCCD]) VALUES (N'51001-02', N'test', N'601040')
47INSERT [dbo].[Mfg_ITMMAST] ([IMPN], [IMDESC], [IMUPCCD]) VALUES (N'51014-02', N'test', N'601040234567')
48INSERT [dbo].[Mfg_ITMMAST] ([IMPN], [IMDESC], [IMUPCCD]) VALUES (N'61001-01', N'test', N'601040')
49
50CREATE PROCEDURE uspScanForBlankUpcs
51AS
52
53 -- setup variables for bringing in blank row data
54 DECLARE @IMPN [varchar](30), @IMUPCCD [varchar](13),
55 @blankUpc [varchar](13), @upcPrefix [varchar](6),
56 @random [varchar](6), @retryRandom bit;
57 SET @blankUpc = '601040'; -- This is the value of IMUPCCD when it is "blank"
58 SET @upcPrefix = '601040'; -- This is prefix for our randomly generated UPC
59
60 -- setup the cursor, query for items with "blank" UPCs
61 DECLARE blanksCursor CURSOR FOR
62 SELECT IMPN
63 FROM [Mfg_ITMMAST]
64 WHERE (LEFT(IMPN, 5) >= '40000' AND
65 LEFT(IMPN, 5) < '60000' AND
66 RIGHT(IMPN, 2) IN ('01','02')) AND
67 IMUPCCD = @blankUpc
68 ;
69
70 -- open the cursor
71 OPEN blanksCursor;
72
73 -- load the next row from the cursor
74 FETCH NEXT FROM blanksCursor
75 INTO @IMPN;
76
77 -- loop through each row of the cursor
78 WHILE @@FETCH_STATUS = 0
79 BEGIN
80 --PRINT 'IMPN: ' + @IMPN;
81 -- try to create a new random number
82 SET @retryRandom = 1;
83 WHILE @retryRandom = 1
84 BEGIN
85 -- get a random number for the UPC, then left-pad it with zeros to 6 digits
86 SET @random = RIGHT('00000' + CONVERT(VARCHAR, FLOOR(RAND() * 999999)), 6);
87 -- concatenate the UPC prefix with the random number
88 SET @IMUPCCD = @upcPrefix + @random
89 --PRINT 'IMUPCCD: ' + @IMUPCCD;
90 -- see if this UPC already exists on another item
91 IF (SELECT COUNT(*) FROM [Mfg_ITMMAST] WHERE [IMUPCCD] = @IMUPCCD) > 0
92 SET @retryRandom = 1; -- UPC already existed (collision) try again
93 ELSE
94 SET @retryRandom = 0; -- didn't already exist, so exit out of loop
95 END
96
97 --PRINT 'Updating...';
98 -- Update the UPC with the random number
99 UPDATE [Mfg_ITMMAST]
100 SET IMUPCCD = @IMUPCCD
101 WHERE IMPN = @IMPN
102 ;
103
104 -- Load the next result
105 FETCH NEXT FROM blanksCursor
106 INTO @IMPN;
107
108 END
109 CLOSE blanksCursor;
110 DEALLOCATE blanksCursor;
111
112GO
113
114exec uspScanForBlankUpcs;
115
116-- Function to output UPC code based on Company Prefix and Item Reference:
117
118CREATE FUNCTION [dbo].[calc_UPC]
119 (@company_prefix varchar(10), @item_reference int)
120RETURNS char(12)
121WITH SCHEMABINDING
122AS
123BEGIN
124declare
125 @upc char(12),
126 @checkdigit int
127
128if SUBSTRING(@company_prefix, 1, 1) = 0
129begin
130
131 set @upc = substring(@company_prefix, 2, 10) + right('000000000000' + ltrim(str(@item_reference)), 12 - len(@company_prefix))
132 set @checkdigit = (1000 - (
133 convert(int, substring(@upc, 1, 1)) * 3 +
134 convert(int, substring(@upc, 2, 1)) * 1 +
135 convert(int, substring(@upc, 3, 1)) * 3 +
136 convert(int, substring(@upc, 4, 1)) * 1 +
137 convert(int, substring(@upc, 5, 1)) * 3 +
138 convert(int, substring(@upc, 6, 1)) * 1 +
139 convert(int, substring(@upc, 7, 1)) * 3 +
140 convert(int, substring(@upc, 8, 1)) * 1 +
141 convert(int, substring(@upc, 9, 1)) * 3 +
142 convert(int, substring(@upc, 10, 1)) * 1 +
143 convert(int, substring(@upc, 11, 1)) * 3)) % 10
144
145 set @upc = rtrim(@upc) + ltrim(str(@checkdigit))
146end
147return @upc
148END
149GO
150
151-- Example Table of products:
152
153CREATE TABLE [dbo].[Product](
154 [product_id] [int] IDENTITY(1,1) NOT NULL,
155 [company_prefix] [varchar](10) NULL,
156 [item_reference] [int] NULL,
157 [upc] AS ([dbo].[calc_UPC]([company_prefix],[item_reference])) PERSISTED,
158 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
159(
160 [product_id] ASC
161))
162ALTER TABLE [dbo].[Product] WITH CHECK ADD CONSTRAINT [item_reference_greater_equal_zero] CHECK (([item_reference]>=(0)))
163GO
164ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [item_reference_greater_equal_zero]
165GO
166
167-- Existing records with UPC codes:
168
169insert product (company_prefix, item_reference) values ( '0601040', 3)
170insert product (company_prefix, item_reference) values ( '0601040', 5)
171
172-- Example of 4 new products without UPC codes
173
174insert product DEFAULT VALUES
175insert product DEFAULT VALUES
176insert product DEFAULT VALUES
177insert product DEFAULT VALUES
178GO
179
180-- Next we need a table of all possible item references.
181-- This is the best implementation I have found for generating numbers:
182
183
184--Creates a table of sequential numbers, useful for all sorts of things
185--Created 08/26/05 by Oskar Austegard from article at
186--http://msdn.microsoft.com/library/en-us/dnsqlpro03/html/sp03k1.asp
187--Limits: @Min and @Max must be between -2147483647 and 2147483647, including.
188--If @Max <= @Min, only a single record with @Min is created
189CREATE FUNCTION [dbo].[NumberTable] (@Min int, @Max int)
190RETURNS @T TABLE (Number int NOT NULL PRIMARY KEY)
191AS
192BEGIN
193 -- Seed the table with the min value
194 INSERT @T VALUES (@Min)
195 --Loop until all the rows are created, inserting ever more records for each iteration (1, 2, 4, etc)
196 WHILE @@ROWCOUNT > 0
197 BEGIN
198 INSERT @T
199 --Get the next values by adding the current max - start value + 1 to each existing number
200 --need to calculate increment value first to avoid arithmetic overflow near limits of int
201 SELECT t.Number + (x.MaxNumber - @Min + 1)
202 FROM @T t
203 CROSS JOIN (SELECT MaxNumber = MAX(Number) FROM @T) x --Current max
204 WHERE
205 --Do not exceed the Max - shift the increment to the right side to take advantage of index
206 t.Number <= @Max - (x.MaxNumber - @Min + 1)
207 END
208 RETURN
209END
210
211GO
212
213-- For 10,000 numbers the performance of this function is good,
214-- but when the range is known I prefer the performance I get with a static table:
215-- Create a table of numbers between 0 and 99999
216CREATE table Numbers (number int)
217insert Numbers (number)
218select n.Number
219from dbo.NumberTable(0, 99999) n
220
221-- Now we can easily assign UPC codes using the available item reference values in your Company Prefix in a single update:
222
223declare @company_prefix varchar(10)
224set @company_prefix = '0601040' -- The function requires the leading zero
225update
226 p
227set
228 item_reference = n.number,
229 company_prefix = @company_prefix
230from
231 (
232 select
233 p.product_id,
234 ROW_NUMBER() OVER (order by product_id) [row]
235 from
236 dbo.product p
237 where
238 p.company_prefix is null
239 ) u
240 inner join dbo.product p on p.product_id = u.product_id
241 inner join
242 (
243 select
244 s.Number,
245 ROW_NUMBER() over (order by s.Number) [row]
246 from
247 (
248 select n.Number from
249 (
250 select
251 n.Number
252 from
253 dbo.Numbers n --Table(@sequence, @size - 1) n
254 left outer join dbo.Product p
255 on p.company_prefix = @company_prefix
256 and n.Number = p.item_reference
257 where
258 p.product_id is null
259 ) n
260 ) s
261 ) n on n.[row] = u.[row]
262GO
263select * from product
264
265CREATE FUNCTION [dbo].[calc_EAN13]
266 (@company_prefix varchar(10), @item_reference int)
267RETURNS char(13)
268WITH SCHEMABINDING
269AS
270BEGIN
271declare
272 @ean13 char(13),
273 @checkdigit int
274
275set @ean13 = @company_prefix
276set @ean13 = @company_prefix + right('0000000000000' + ltrim(str(@item_reference)), 12 - len(@company_prefix))
277set @checkdigit = (1000 - (
278 convert(int, substring(@ean13, 1, 1)) * 1 +
279 convert(int, substring(@ean13, 2, 1)) * 3 +
280 convert(int, substring(@ean13, 3, 1)) * 1 +
281 convert(int, substring(@ean13, 4, 1)) * 3 +
282 convert(int, substring(@ean13, 5, 1)) * 1 +
283 convert(int, substring(@ean13, 6, 1)) * 3 +
284 convert(int, substring(@ean13, 7, 1)) * 1 +
285 convert(int, substring(@ean13, 8, 1)) * 3 +
286 convert(int, substring(@ean13, 9, 1)) * 1 +
287 convert(int, substring(@ean13, 10, 1)) * 3 +
288 convert(int, substring(@ean13, 11, 1)) * 1 +
289 convert(int, substring(@ean13, 12, 1)) * 3)) % 10
290
291set @ean13 = rtrim(@ean13) + ltrim(str(@checkdigit))
292return @ean13
293END
294GO