December 14, 2009 at 12:05 pm
I am trying to retrieve one record/data at a time from a table below:
Address
--------
New York
Maryland
Chicago
Iowa
declare @var int
set @var=1
while @var <= (select COUNT(*) from mytable)
begin
//Here, I need to use each address for other calculation
//How to get New York, then Maryland, and so on
set @var=@var+1
end
Is there anyway to do this?
Thanks in advance !!!
December 14, 2009 at 12:11 pm
If you present your data as discussed in the link in my signature block, and be very specific about what you desire to do with this data I would be willing to bet that people here on SSC will be able to provide you with a Set Based solution ... Give it a try and see what assistance you obtain
Edited 2:13 correct spelling error
December 14, 2009 at 12:12 pm
Yes, a loop or cursor will allow you to get one row at a time, but there are very few instances where this is needed. The vast majority of apparent row-by-row operations can be done with SET logic. SET logic will prove to be much superior in terms of performance.
So, what do you want to do with the rows once you have them? What sort of 'other calulation' do you need to do?
December 14, 2009 at 12:45 pm
Actually I am trying to search in a table using comma separated value.
I found the code in forum section to convert comma separated string to table
And I am trying to fit this code to solve my problem
alter procedure web_ParseArray
( @Array varchar(1000),
@separator char(1) )
AS
set nocount on
declare @separator_position int
declare @array_value varchar(1000)
create table #ParsedArrays (array_Value varchar(1000))
set @array = @array + @separator
while patindex('%' + @separator + '%' , @array) <> 0
begin
select @separator_position = patindex('%' + @separator + '%' , @array)
select @array_value = left(@array, @separator_position - 1)
insert #ParsedArrays VALUES (@array_value)
select @array = stuff(@array, 1, @separator_position, '')
end
set nocount off
declare @var int
declare @sqlquery varchar(200)
set @sqlquery='select * from Employee where '
set @var=1
declare @arrValue varchar(50)
while @var <= (select COUNT(*) from #ParsedArrays)
begin
--trying to get each row from the table here
set @arrValue='table row value'
--Here, I am trying to use each value
set @sqlquery=@sqlQuery+ 'Address like %'+ @arrValue +'% AND'
set @var=@var+1
end
--need to Remove last AND
drop table #ParsedArrays
go
December 14, 2009 at 12:59 pm
I am sure that there is a better way to do what you are asking. Problem is, I'm not exactly sure what you are trying to accomplish. Please read the first article I have referenced below in my signature block; follow the instructions to post table DDL and sample data. Also, be sure to post the expected results based on the sample data.
Once we have that, we can show you a set-based way of doing the same thing that will work faster, and you will get tested code in return as well for the extra effort you put in to posting wha twe need to help you.
December 14, 2009 at 1:12 pm
Try this in your test/dev database:
create table dbo.Numbers (
Number int primary key);
go
insert into dbo.Numbers (Number)
select top 10001 row_number() over (order by t1.object_id) - 1
from sys.columns t1
cross join sys.columns t2;
go
create procedure web_ParseArray
(@Array varchar(1000),
@Separator char(1))
as
set nocount on;
;with CTE_Values (Array_Value) as
(select substring(@Array + @Separator, Number, charindex(@Separator, @Array + @Separator, Number) - Number)
from dbo.Numbers
where Number <= len(@Array)
and substring(@Separator + @Array, Number, 1) = @Separator)
select distinct Employee.*
from Employee
inner join CTE_Values
on Employee.Address like '%' + CTE_Values.Array_Value + '%';
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 14, 2009 at 1:49 pm
GSquared,
Thank you very much.
This is the perfect solution.
create table dbo.Numbers (
Number int primary key);
go
insert into dbo.Numbers (Number)
select top 10001 row_number() over (order by t1.object_id) - 1
from sys.columns t1
cross join sys.columns t2;
go
-- [web_ParseArray] 'New, York', ','
ALTER procedure [dbo].[web_ParseArray]
(@Array varchar(1000),
@Separator char(1))
as
set nocount on;
with CTE_Values (Array_Value) as
(select substring(@Array + @Separator, Number, charindex(@Separator, @Array + @Separator, Number) - Number)
from dbo.Numbers
where Number <= len(@Array)
and substring(@Separator + @Array, Number, 1) = @Separator)
select distinct Emp_Id
from Employee
inner join CTE_Values
on Address like '%' + CTE_Values.Array_Value + '%';
Emp_ID Name Address Phone
-----------------------------------------------------
1 Adam New York 242-234-2433
2 Smith New Jersey 232-345-5645
3 John North Iowa 465-567-7543
4 Ajay South New York456-576-5756
5 Tony Northern London353-533-4665
6 Bush Old York 466-354-3543
After executing this command, I got the answer I am expecting
exec web_ParseArray 'New,York', ','
Emp_ID Name Address Phone
------------------------------------------------------
1 Adam New York 242-234-2433
2 Smith New Jersey 232-345-5645
4 Ajay South New York456-576-5756
6 Bush Old York 466-354-3543
This is the one I am looking for.
Thank you everyone helping me to solve the problem.
Regards,
Romah
December 14, 2009 at 1:51 pm
You're welcome.
When you have a chance (make it soon), look up Numbers and Tally tables on this site, and via Google/Bing/whatever. They're very, very useful.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 14, 2009 at 9:34 pm
romah (12/14/2009)
Actually I am trying to search in a table using comma separated value.I found the code in forum section to convert comma separated string to table
And I am trying to fit this code to solve my problem
alter procedure web_ParseArray
( @Array varchar(1000),
@separator char(1) )
AS
set nocount on
declare @separator_position int
declare @array_value varchar(1000)
create table #ParsedArrays (array_Value varchar(1000))
set @array = @array + @separator
while patindex('%' + @separator + '%' , @array) <> 0
begin
select @separator_position = patindex('%' + @separator + '%' , @array)
select @array_value = left(@array, @separator_position - 1)
insert #ParsedArrays VALUES (@array_value)
select @array = stuff(@array, 1, @separator_position, '')
end
set nocount off
declare @var int
declare @sqlquery varchar(200)
set @sqlquery='select * from Employee where '
set @var=1
declare @arrValue varchar(50)
while @var <= (select COUNT(*) from #ParsedArrays)
begin
--trying to get each row from the table here
set @arrValue='table row value'
--Here, I am trying to use each value
set @sqlquery=@sqlQuery+ 'Address like %'+ @arrValue +'% AND'
set @var=@var+1
end
--need to Remove last AND
drop table #ParsedArrays
go
No... you don't need to do it that way. You can split a whole table's worth of CSV's in a set based manner. Please see the following article. Once you understand how a Tally table works, there's code near the bottom of the article to split a whole table.
The article was written relatively a long time ago. There are even faster ways to do this using the Tally table and an Inline Table Valued function.
Here's the URL to the article:
http://www.sqlservercentral.com/articles/T-SQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
December 14, 2009 at 9:38 pm
Heh... ack! Serves me right for not reading the whole post before responding.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply