looping through recordset

hello,
i have a select query that returns multiple rows (within a cursor). How do i loop through the rows to process it (in a stored proc)? I donot want to use nested cursors. a code sample is requested.
thanks!Usually a cursor is created to process multiple rows (unless you developed a passion for cursors and would like to create one for 1 row).

Have you looked at FETCH NEXT?|||Well, the situation is somethin like this. I am using a cursor that returns multiple rows, within the while @.@.FETCH_STATUS = 0, I have another sql select that returns multiple rows for every row the cursor is processing. I would want to process every row returned in the inner select query. Do I have an option other than using nested cursors? Any help is appreciated.|||If you post the code maybe we can get rid of the cursor.

loop through each table of my DB

Is there a way to loop using a cursor in SQL-server so i can see if each columns of each tables that i loop through my DB have a specific string value and change it to something else, renaming the column if the match if correct.

any threads that i can read from or website..

thanx !!Won't you get there using:SELECT * FROM INFORMATION_SCHEMA.COLUMNS-PatP|||Table name = nMontantBilletTVQ
Table name = nMontantBilletTPS

here an example of what i try to find.

i would like to search the word 'TVQ' in my string 'nMontantBilletTVQ'

is there a string functions for that ?? just cant get one working

thanx|||Use LIKE.

-PatP

Loop / Cursor help

Having a brain cramp here and don't know where to start. I have 2 tables:
vehicles and vehicle_useage. What I would like to do is this:
For each distinct vehicle in the vehicle table, I want to make entries for
each day of the month taken from a given date. This routine will be
scheduled to fire off once a month and populate the vehicle_useage table wit
h
vehicle use_dates for each day of the current month and for each VIN from th
e
vehicle table.
vehicle table:
VIN emp_id
-- --
VIN123456789 620123
VIN987654321 620123
vehicle_useage table:
use_date VIN miles
-- -- --
02/01/2006 VIN123456789 0
02/02/2006 VIN123456789 0
02/03/2006 VIN123456789 0
02/04/2006 VIN123456789 0
etc...
02/01/2006 VIN987654321 0
02/02/2006 VIN987654321 0
02/03/2006 VIN987654321 0
02/04/2006 VIN987654321 0
etc...
Much appreciated for any help you can give...Re-think your design. Why not just make an entry when the vehicle is
actually used?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"woodfoot" <woodfoot@.discussions.microsoft.com> wrote in message
news:760E1B4E-BD51-401B-A62B-DB68038881DB@.microsoft.com...
Having a brain cramp here and don't know where to start. I have 2 tables:
vehicles and vehicle_useage. What I would like to do is this:
For each distinct vehicle in the vehicle table, I want to make entries for
each day of the month taken from a given date. This routine will be
scheduled to fire off once a month and populate the vehicle_useage table
with
vehicle use_dates for each day of the current month and for each VIN from
the
vehicle table.
vehicle table:
VIN emp_id
-- --
VIN123456789 620123
VIN987654321 620123
vehicle_useage table:
use_date VIN miles
-- -- --
02/01/2006 VIN123456789 0
02/02/2006 VIN123456789 0
02/03/2006 VIN123456789 0
02/04/2006 VIN123456789 0
etc...
02/01/2006 VIN987654321 0
02/02/2006 VIN987654321 0
02/03/2006 VIN987654321 0
02/04/2006 VIN987654321 0
etc...
Much appreciated for any help you can give...|||I know what you are saying and I agree with you 110%. This is a temp
solution for something being re-designed anyway. It's life span may be 3
months max.
Sadly I must say, I just need a quick bandaid fix...
"Tom Moreau" wrote:

> Re-think your design. Why not just make an entry when the vehicle is
> actually used?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "woodfoot" <woodfoot@.discussions.microsoft.com> wrote in message
> news:760E1B4E-BD51-401B-A62B-DB68038881DB@.microsoft.com...
> Having a brain cramp here and don't know where to start. I have 2 tables:
> vehicles and vehicle_useage. What I would like to do is this:
> For each distinct vehicle in the vehicle table, I want to make entries for
> each day of the month taken from a given date. This routine will be
> scheduled to fire off once a month and populate the vehicle_useage table
> with
> vehicle use_dates for each day of the current month and for each VIN from
> the
> vehicle table.
> vehicle table:
> VIN emp_id
> -- --
> VIN123456789 620123
> VIN987654321 620123
> vehicle_useage table:
> use_date VIN miles
> -- -- --
> 02/01/2006 VIN123456789 0
> 02/02/2006 VIN123456789 0
> 02/03/2006 VIN123456789 0
> 02/04/2006 VIN123456789 0
> etc...
> 02/01/2006 VIN987654321 0
> 02/02/2006 VIN987654321 0
> 02/03/2006 VIN987654321 0
> 02/04/2006 VIN987654321 0
> etc...
> Much appreciated for any help you can give...
>|||woodfoot wrote:
> I know what you are saying and I agree with you 110%. This is a temp
> solution for something being re-designed anyway. It's life span may be 3
> months max.
> Sadly I must say, I just need a quick bandaid fix...
>
Replied in comp.databases.ms-sqlserver
Please don't multipost.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Loop / Cursor help

Having a brain cramp here and don't know where to start. I have 2 tables:
vehicles and vehicle_useage. What I would like to do is this:

For each distinct vehicle in the vehicle table, I want to make entries
for each day of the month taken from a given date. This routine will be
scheduled to fire off once a month and populate the vehicle_useage table
with vehicle use_dates for each day of the current month and for each VIN
from the vehicle table.

vehicle table:

VIN emp_id
---- --
VIN123456789 620123
VIN987654321 620123

vehicle_useage table:

use_date VIN miles
---- ---- --
02/01/2006 VIN123456789 0
02/02/2006 VIN123456789 0
02/03/2006 VIN123456789 0
02/04/2006 VIN123456789 0
etc...
02/01/2006 VIN987654321 0
02/02/2006 VIN987654321 0
02/03/2006 VIN987654321 0
02/04/2006 VIN987654321 0
etc...

Much appreciated for any help you can give...woodfoot wrote:
> Having a brain cramp here and don't know where to start. I have 2 tables:
> vehicles and vehicle_useage. What I would like to do is this:
> For each distinct vehicle in the vehicle table, I want to make entries
> for each day of the month taken from a given date. This routine will be
> scheduled to fire off once a month and populate the vehicle_useage table
> with vehicle use_dates for each day of the current month and for each VIN
> from the vehicle table.
> vehicle table:
> VIN emp_id
> ---- --
> VIN123456789 620123
> VIN987654321 620123
> vehicle_useage table:
> use_date VIN miles
> ---- ---- --
> 02/01/2006 VIN123456789 0
> 02/02/2006 VIN123456789 0
> 02/03/2006 VIN123456789 0
> 02/04/2006 VIN123456789 0
> etc...
> 02/01/2006 VIN987654321 0
> 02/02/2006 VIN987654321 0
> 02/03/2006 VIN987654321 0
> 02/04/2006 VIN987654321 0
> etc...
> Much appreciated for any help you can give...

Use a Calendar table (one row per date):

INSERT INTO vehicle_useage (use_date, vin, miles)
SELECT C.cal_date, V.vin, 0
FROM vehicles AS V
JOIN calendar AS C
ON C.cal_date BETWEEN '20060201' AND 20060228' ;

Alternatively perhaps it would be better to insert the useage rows only
when you want to update the mileage figure. You can still report on
each day by joining to the calendar.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Please don't multi-post. I answered this in
microsoft.public.sqlserver.programming.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"woodfoot" <jdperry784@.yahoo.com> wrote in message
news:Xns977055E12663Ejamesperrycomcastnet@.216.196. 97.136...
Having a brain cramp here and don't know where to start. I have 2 tables:
vehicles and vehicle_useage. What I would like to do is this:

For each distinct vehicle in the vehicle table, I want to make entries
for each day of the month taken from a given date. This routine will be
scheduled to fire off once a month and populate the vehicle_useage table
with vehicle use_dates for each day of the current month and for each VIN
from the vehicle table.

vehicle table:

VIN emp_id
---- --
VIN123456789 620123
VIN987654321 620123

vehicle_useage table:

use_date VIN miles
---- ---- --
02/01/2006 VIN123456789 0
02/02/2006 VIN123456789 0
02/03/2006 VIN123456789 0
02/04/2006 VIN123456789 0
etc...
02/01/2006 VIN987654321 0
02/02/2006 VIN987654321 0
02/03/2006 VIN987654321 0
02/04/2006 VIN987654321 0
etc...

Much appreciated for any help you can give...|||Sorry, I have not posted to USENET groups before.

"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in news:sKjKf.3300$%14.163054
@.news20.bellglobal.com:

> Please don't multi-post. I answered this in
> microsoft.public.sqlserver.programming.|||Is there a way to do this without the use of a calendar table?
thx|||woodfoot wrote:
> Is there a way to do this without the use of a calendar table?
> thx

You could create the calendar in a table variable. It seems a bit
pointless to do that each month though. Calendar tables are useful for
all sorts of queries and reports. It's probably worth keeping one in
your database whether you use it for this or not.

DECLARE @.t TABLE (cal_date DATETIME PRIMARY KEY);
DECLARE @.month DATETIME;

SET @.month = '20060201';

INSERT INTO @.t VALUES (@.month);

WHILE (SELECT MAX(cal_date) FROM @.t)<DATEADD(MONTH,1,@.month)
INSERT INTO @.t (cal_date)
SELECT DATEADD(DAY,
DATEDIFF(DAY,@.month,cal_date)+1,
(SELECT MAX(cal_date) FROM @.t))
FROM @.t;

INSERT INTO vehicle_useage (use_date, vin, miles)
SELECT C.cal_date, V.vin, 0
FROM vehicles AS V
JOIN @.t AS C
ON C.cal_date >= @.month
AND C.cal_date < DATEADD(MONTH,1,@.month);

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Beautiful. Worked great, thanks a bunch David.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
news:1140447821.251324.128220@.z14g2000cwz.googlegr oups.com:

> woodfoot wrote:
>> Is there a way to do this without the use of a calendar table?
>> thx
> You could create the calendar in a table variable. It seems a bit
> pointless to do that each month though. Calendar tables are useful for
> all sorts of queries and reports. It's probably worth keeping one in
> your database whether you use it for this or not.
> DECLARE @.t TABLE (cal_date DATETIME PRIMARY KEY);
> DECLARE @.month DATETIME;
> SET @.month = '20060201';
> INSERT INTO @.t VALUES (@.month);
> WHILE (SELECT MAX(cal_date) FROM @.t)<DATEADD(MONTH,1,@.month)
> INSERT INTO @.t (cal_date)
> SELECT DATEADD(DAY,
> DATEDIFF(DAY,@.month,cal_date)+1,
> (SELECT MAX(cal_date) FROM @.t))
> FROM @.t;
> INSERT INTO vehicle_useage (use_date, vin, miles)
> SELECT C.cal_date, V.vin, 0
> FROM vehicles AS V
> JOIN @.t AS C
> ON C.cal_date >= @.month
> AND C.cal_date < DATEADD(MONTH,1,@.month);
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/...US,SQL.90).aspx
> --|||David,

Why does select * from @.t returns 32 rows?
I expected it to return 28 rows only

Madhivanan|||Madhivanan (madhivanan2001@.gmail.com) writes:
> Why does select * from @.t returns 32 rows?
> I expected it to return 28 rows only

Watch this part of the output:

(1 row(s) affected)

(1 row(s) affected)

(2 row(s) affected)

(4 row(s) affected)

(8 row(s) affected)

(16 row(s) affected)

See what is going on? David is doubling the number of rows he inserts
each time. That way the loops can run fewer iterations and be faster.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland

Madhivanan

Looking for duplicate and consecutive records in a table

I'm trying to get the LastcallResult = 'Not Home' result that happen more than once but that happen in consecutive order. This cursor displays the records but doesn't grab the occurance one after the other , consecutive order.

If there's a way I can modify this cursor to get the records I want or If there's another process to get the records that have the "not home' filter more than once in consecutive order .

Thanks for any suggestions in advance....

DECLARE get_lastCallresult CURSOR FOR

select count(*), lastCallresult, fn, ln from contacts

where lastCallresult = 'Not Home'

and contactType = 'referral'

and system = 'lead'

--and callback ='Not Home'

group by lastCallresult,fn, ln

having COUNT(*) > 2

OPEN get_lastCallresult

FETCH NEXT FROM get_lastCallresult

WHILE @.@.FETCH_STATUS = 0

FETCH NEXT FROM get_lastCallresult

CLOSE get_lastCallresult

DEALLOCATE get_lastCallresult

Try adding identity() to your select

Code Snippet

select count(*), lastCallresult, fn, ln from contacts, IDENTITY(int, 1,1) as seq

where lastCallresult = 'Not Home'

and contactType = 'referral'

and system = 'lead'

--and callback ='Not Home'

group by lastCallresult,fn, ln

having COUNT(*) > 2

|||

For this kind of questions, it is better to post some DDL, including constraints and indexes, sample data and expected result.

AMB

|||

Are you sure that you require a CURSOR based operation?

I suggest that you consider dropping the CURSOR and just create a #temp TABLE with the query results. Then you can cycle through the temp table if your needs require rowwise operations.

However, if you provided the code that you use on the cursor, we might be able to help you revise the code to do it all in one operation. SQL

Server is best when used for SET based operations -not 'recordset' type operations.

|||

Try adding an ORDER BY clause to your statement. Cursor operations are ordered, but you have to specify the order by for ordering to be guaranteed.

Without more info, no idea if you actually need a cursor, but look into the ROW_NUMBER() function if this is SQL Server 2005. It will let you do a lot of cool things in regards to gettting the last row.

DaleJ, this isn't valid syntax, certainly not in the FROM clause, and not in the SELECT clause without and INTO:

select count(*), lastCallresult, fn, ln

from contacts, IDENTITY(int, 1,1) as seq

where lastCallresult = 'Not Home'

and contactType = 'referral'

and system = 'lead'

--and callback ='Not Home'

group by lastCallresult,fn, ln

having COUNT(*) > 2

|||

Oops, yup, thanks Louis.

I didn't think that all the way through along with not paying attention to where I pasted...

|||

The only reason the I'm trying a cursor is because I was trying to scan thru the rows and find all those rows that are repeated and happen one after the other

i.e. ( this is not the actual table, is something to demostrate)

Name Address Phone DateCalled CalledReason

John 123 Main St 123-1234 01/02/06 Survey

John 123 Main St 123-1234 02/25/06 Survey

The column the has the called reason is the one that I want, is repeated and happen one after the oher.

|||

druiz,

The solution would be finding existing groups, of consecutive rows, with more than one element. If you are using SQL Server 2005, then you can try using the new ranking function ROW_NUMBER.

Code Snippet

create table dbo.t1 (

[Name] varchar(50),

[Address] varchar(50),

Phone char(8),

DateCalled datetime,

CalledReason varchar(25)

)

go

insert into dbo.t1 values('John', '123 Main St', '123-1234', '01/01/06', 'Reason 1')

insert into dbo.t1 values('John', '123 Main St', '123-1234', '01/02/06', 'Survey')

insert into dbo.t1 values('John', '123 Main St', '123-1234', '02/25/06', 'Survey')

insert into dbo.t1 values('John', '123 Main St', '123-1234', '02/26/06', 'Reason 2')

insert into dbo.t1 values('John', '123 Main St', '123-1234', '02/27/06', 'Reason 3')

insert into dbo.t1 values('John', '123 Main St', '123-1234', '02/28/06', 'Reason 3')

insert into dbo.t1 values('Paul', '124 Main St', '123-5678', '01/02/06', 'Reason 1')

insert into dbo.t1 values('Paul', '124 Main St', '123-5678', '01/03/06', 'Reason 2')

go

;with cte

as

(

select

[name], [address], phone, datecalled, calledreason,

row_number() over(partition by [name], [address], phone order by datecalled) -

row_number() over(partition by [name], [address], phone, calledreason order by datecalled) as grp

from

dbo.t1

)

select

[name],

[address],

phone,

calledreason,

min(datecalled) as min_datecalled,

max(datecalled) as max_datecalled

from

cte

group by

[name], [address], phone, calledreason, grp

having

count(*) > 1

go

drop table dbo.t1

go

AMB

|||

This snippet has been really helpful. I tried this same way and it gave the results I'm looking for, I'm just going to make a couple mod's to fit my tables.

Thanks