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
 General SQL Server Forums
 New to SQL Server Programming
 create procedure

Author  Topic 

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2014-04-21 : 02:21:14
hi guys

my sql code so


delete from numbers

declare @i as int
set @i = 3440000

while(@i < 3442047)
begin
insert into numbers values(@i)
set @i = @i+1
end

select ph from numbers where ph not in(
select phone from
phone where phone >=3440000 and phone<=3442047
)





query works fine
and
Results 144 query strings


3442047 changed to @ t1

and created a procedure




create alter proc trt
@i int,
@t1 int
as
delete from numbers
while(@i < @t1)
begin
insert into numbers values(@i)
set @i = @i+1
end
select ph from numbers where ph not in(
select phone from
phone where phone >=@i and phone<=@t1
)


exec dbo.trt @i=3440000,@t1=3442047 


but Results this query

2047 lines

means a procedure does not work
this script

select ph from numbers where ph not in(
select phone from
phone where phone >=@i and phone<=@t1
)


someone can advise how I can change this procedure?
To have a good Results

thank's

http://sql-az.tr.gg/

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2014-04-21 : 08:30:18

I changed the structure of the procedure
so
created a new parameter @ t2
now works good
Results and got what I want

alter proc trt
@t1 int,
@t2 int
as
delete from numbers
declare @i int
set @i=@t1
while(@i <@t2)
begin
insert into numbers values(@i)
set @i = @i+1
end
select ph from numbers where ph not in(
select phone from
phone where phone >=@t1 and phone<=@t2
)

thank?s all
my friends

http://sql-az.tr.gg/
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2014-04-21 : 08:36:38
You can do this in a single statement

;with cte as
(
select i = @t1
union all
select i = i + 1 from cte where i+1 < @t2
)
select ph = i
from cte
left join phone p
on p.phone = cte.i
where p.phone is null


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -