help: procedure - SQL Server Forums

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

Author

Topic

shalahuddin
Starting Member

7 Posts

Posted - 2010-03-01 : 01:42:47

dear all my friends
i write a query, when i execute that query. show error message
invalid column name 'num'
this is my query:

--step 1
create table dbo.master_mst(customer_serial_code varchar(9) not null, customer_permanent_code varchar(13) not null, nama varchar(10))

--step2
insert into master_mst values ('121345526','1271213455263','jonathan')
insert into master_mst values ('194028374','1271940283740','david')

--step3
create Procedure dummy
as
begin

declare @routing_number varchar(50)
declare @routing_number2 varchar(50)
declare @routing_number3 varchar(50)
declare @digit int
declare @digit4 varchar(10)
declare @digit8 varchar(8)
declare @running_number1 int
declare @running_number2 int
declare @sum_digit int
declare @check_digit int
declare @numberWithCheckDigit char(9)
set @check_digit=0
set @running_number1 = (select count(customer_serial_code)
from master_mst)
set @running_number2 = 0
declare @numgen table (num int)
while @running_number2 <= @running_number1
begin
set @running_number2 = @running_number2 + 1
insert into @numgen
select case when num < 0 then'-' else '' end + right('0000000' + replace(num, '-',''),7)
end

declare csrdigit4 cursor for

select customer_serial_code
from master_mst

open csrdigit4
fetch next from csrdigit4
into @routing_number2
while @@fetch_status = 0
begin
set @digit4 = substring(@routing_number2, 1, 1)

set @digit8 = @digit4 + convert(varchar(7),(num))

create table #8digit(digit8 varchar(8))
insert into #8digit
select @digit8 as digit8

fetch next from csrdigit4
into @routing_number2
end

close csrdigit4
deallocate csrdigit4

declare csrdigit cursor for

select digit8
from #8digit

open csrdigit
fetch next from csrdigit
into @routing_number
while @@fetch_status = 0
begin

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

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

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

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

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

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

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

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

set @numberWithCheckDigit = @digit8 + convert(char(1),(@check_digit))

create table #idserial (serialWithGTIN8 varchar(9))
insert into #idserial
select @numberWithCheckDigit as serialWithGTIN8

fetch next from csrdigit
into @routing_number
end

close csrdigit
deallocate csrdigit

declare @idPer varchar(3)
set @idPer = '909'
declare @12digit varchar(12) = @idPer + @numberWithCheckDigit
create table #number12Digit(digit12 varchar(12))
insert into #number12Digit
select @12digit as digit12

declare csrDigit13 cursor for

select digit12
from #number12Digit

open csrDigit13
fetch next from csrDigit13
into @routing_number3
while @@fetch_status = 0
begin

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

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

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

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

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

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

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

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

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

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

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

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

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

declare @numberWithCheckDigit2 varchar(13)
set @numberWithCheckDigit2 = @12digit + @check_digit

create table #no_id(noWithGTIN13 varchar(13))
insert into #no_id
select @numberWithCheckDigit2 as noWithGTIN13

fetch next from csrDigit13
into @routing_number3
end

close csrDigit13
deallocate csrDigit13

select customer_serial_code, customer_permanent_code,
@numberWithCheckDigit as id_serial, @numberWithCheckDigit as no_id into dummy_number from master_mst

end

I hope you can help me....
Thanx be for

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-01 : 02:04:16

The following, it should be @running_number2 instead of num ?


declare @numgen table (num int)
while @running_number2 <= @running_number1
begin
    set @running_number2 = @running_number2 + 1
    insert into @numgen
    select case when num < 0 then'-' else '' end + right('0000000' + replace(num, '-',''),7)
end

there is another section where you also reference to the 'num' without selection for table that contains the column num

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

shalahuddin
Starting Member

7 Posts

Posted - 2010-03-01 : 04:22:04

thanx for your reply...
I have try your instructions. i replace num with @running_number2.
procedure successfully created. but when i execute procedure dummy.
show the message "there is already an object named '#8digit' in the database"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources