Help: Procedure to create new table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help: Procedure to create new table

Author

Topic

shalahuddin
Starting Member

7 Posts

Posted - 2010-03-09 : 22:04:30

hay friends, I have a problem with procedure, i hope you all can help me. I write a query like this above:

create table master_mst(permanent varchar(13) not null,
serial varchar(9) not null,nama varchar(10))

insert into master_mst values ('1274156429509','415642950','jonathan')
insert into master_mst values ('1272565562652','256556265','Rina')
insert into master_mst values ('1273658925954','365892595','agustina')
insert into master_mst values ('1279262322322','926232232','ryan')

and the result like this above:
master_mst
-------------------------------------|
permanent |serial | Nama |
-------------------------------------|
1274156429509 | 415642950 | jonathan |
1272565562652 | 256556265 | Rina |
1273658925954 | 365892595 | Agustina |
1279262322322 | 926232232 | ryan |
-------------------------------------|

And i qreate procedure like this above:
create procedure dbo.dummy2
as
begin

declare @routing_number varchar (20)
declare @routing_number2 varchar (20)
declare @digit int
declare @sum_digit int
declare @check_digit int

declare @idper varchar(3)
set @idper = '909'

declare @dgOld varchar(1)
declare @dgOld2 varchar(20)
declare @a int
declare @b int
declare @aConv varchar(8)
declare @coba varchar (11)
declare @GTIN8 varchar(9)
declare @GTIN13 varchar(13)
set @a = 0
set @b = (select count(serial)
from master_mst)

declare csrdgOld cursor for
select permanent
from master_mst

open csrdgOld
fetch next from csrdgOld
into @routing_number
while @@fetch_status = 0
begin
set @dgOld = substring(@routing_number, 4, 1)

fetch next from csrdgOld
into @routing_number
end

close csrdgOld
deallocate csrdgOld

create table #EAN8(EAN8 varchar(9))

create table #EAN13(EAN13 varchar(13))
while @a < @b
begin
set @a = @a + 1

set @a = convert(varchar(7),(@a))

set @aConv = right('0000000' + replace(@a, '-',''),7)

--1
set @digit =convert(int, substring(@aConv, 1, 1))
set @sum_digit = @digit*3

--2
set @digit =convert(int, substring(@aConv, 2, 1))
set @sum_digit = @sum_digit + @digit*1

--3
set @digit =convert(int, substring(@aConv, 3, 1))
set @sum_digit = @sum_digit + @digit*3

--4
set @digit =convert(int, substring(@aConv, 4, 1))
set @sum_digit = @sum_digit + @digit*1

--5
set @digit =convert(int, substring(@aConv, 5, 1))
set @sum_digit = @sum_digit + @digit*3

--6
set @digit =convert(int, substring(@aConv, 6, 1))
set @sum_digit = @sum_digit + @digit*1

--7
set @digit =convert(int, substring(@aConv, 7, 1))
set @sum_digit = @sum_digit + @digit*3

if (@sum_digit % 10) > 0
set @check_digit = 10 - (@sum_digit % 10)

set @GTIN8 = substring(@aConv, 1, 8) + convert(char(1), (@check_digit))

insert into #EAN8
select @GTIN8 as EAN8
end

declare csrdgEAN13 cursor for
select EAN8
from #EAN8

open csrdgEAN13
fetch next from csrdgEAN13
into @routing_number2
while @@fetch_status = 0
begin
set @dgOld2 = substring(@routing_number2, 1, 9)

set @coba = @idper + @dgOld2

--1
set @digit =convert(int, substring(@coba, 1, 1))
set @sum_digit = @digit*1

--2
set @digit =convert(int, substring(@coba, 2, 1))
set @sum_digit = @sum_digit + @digit*3

--3
set @digit =convert(int, substring(@coba, 3, 1))
set @sum_digit = @sum_digit + @digit*1

--4
set @digit =convert(int, substring(@coba, 4, 1))
set @sum_digit = @sum_digit + @digit*3

--5
set @digit =convert(int, substring(@coba, 5, 1))
set @sum_digit = @sum_digit + @digit*1

--6
set @digit =convert(int, substring(@coba, 6, 1))
set @sum_digit = @sum_digit + @digit*3

--7
set @digit =convert(int, substring(@coba, 7, 1))
set @sum_digit = @sum_digit + @digit*1

--8
set @digit =convert(int, substring(@coba, 8, 1))
set @sum_digit = @sum_digit + @digit*3

--9
set @digit =convert(int, substring(@coba, 9, 1))
set @sum_digit = @sum_digit + @digit*1

--10
set @digit =convert(int, substring(@coba, 10, 1))
set @sum_digit = @sum_digit + @digit*3

--11
set @digit =convert(int, substring(@coba, 11, 1))
set @sum_digit = @sum_digit + @digit*1

--12
set @digit =convert(int, substring(@coba, 12, 1))
set @sum_digit = @sum_digit + @digit*3

if (@sum_digit % 10) > 0
set @check_digit = 10 - (@sum_digit % 10)

set @GTIN13 = @idper + @dgOld + @dgOld2 + convert(char(1), (@check_digit))

insert into #EAN13
select @GTIN13 as EAN13

fetch next from csrdgEAN13
into @routing_number2
end

close csrdgEAN13
deallocate csrdgEAN13

select * from #EAN8
select * from #EAN13

select master_mst.permanent, master_mst.serial,
@dgOld2 as id_serial, @GTIN13 as id_permanent into dummy_number from master_mst

end

exec dummy2

and the result from that's like this above:
dummy_number
----------------------------------------------------------------|
permanent | serial | id_serial | id_permanent |
----------------------------------------------------------------|
1274156429509 | 415642950 | 00000048 | 9099000000482 |
1272565562652 | 256556265 | 00000048 | 9099000000482 |
1273658925954 | 365892595 | 00000048 | 9099000000482 |
1279262322322 | 926232232 | 00000048 | 9099000000482 |
----------------------------------------------------------------|

but the result i want not like that's. I want the result like this above:

dummy_number
------------------------------------------------------|
permanent | serial | id_serial | id_permanent |
------------------------------------------------------|
1274156429509 | 415642950 | 400000017 | 9094000000178 |
1272565562652 | 256556265 | 200000024 | 9092000000246 |
1273658925954 | 365892595 | 300000031 | 9093000000311 |
1279262322322 | 926232232 | 900000048 | 9099000000482 |
------------------------------------------------------|

realy needy help for this case. Thanx before

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-10 : 00:01:23

I didn't go through the details of you code. But looks like you are assigning running serial no to the number and calculating the check digit for EAN 8 / EAN 13.

Since you are using SQL 2005, you can use row_number() to

You could write a function to calculate the check digit for EAN 8 & EAN 13

here is a quick one i wrote


CREATE  FUNCTION sfn_ean_chkdigit
(	
	@barcode	varchar(20)
)
RETURNS CHAR(1)
AS
BEGIN
	DECLARE
		@chk_digit	int,
		@chk		int

	DECLARE	@num TABLE
	(
		num	int
	)

	IF	LEN(@barcode) NOT IN (7, 12)
	BEGIN
		RETURN 	NULL
	END

	INSERT INTO @num 
	SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL  SELECT  5 UNION ALL SELECT  6 UNION ALL
	SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12

	SELECT	@chk_digit = SUM(CONVERT(int, SUBSTRING(@barcode, LEN(@barcode) - num + 1, 1)) * CASE WHEN num % 2 = 1 THEN 3 ELSE 1 END)
	FROM	@num
	WHERE	num	<= LEN(@barcode)

	SELECT	@chk_digit = (10 - (@chk_digit % 10)) % 10

 	RETURN  CHAR(ASCII('0') + @chk_digit)
END

and then make use of to calculate the check digit


select	*, id_serial = ids + dbo.sfn_ean_chkdigit(ids)
from
(
	select	*, ids = right(replicate('0', 7) + convert(varchar(10), row_number() over (order by [permanent])), 7)
	from	master_mst
) d

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources