From Table to a set of Parameters / Variables

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 2000 Forums

SQL Server Development (2000)

From Table to a set of Parameters / Variables

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-12 : 08:19:47

How do I take data from a table and shift the data, one row at a time, into a set of variables, ready for use?

And cycle thru a loop?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 08:24:22

That is not a bad idea.

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-12 : 08:24:43

Yes if you have identity column in the table, you can use simple WHILE loop to loop through all records.

Select @i=Min(ID), @iMax = Max(ID) from table

While @i <= @iMax and @i IS NOT NULL
Begin
  -- Do processing here
  ....

 
  Select @i = Min(ID) from table where ID > @i
End

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-12 : 08:25:56

quote:
Originally posted by mikebird

How do I take data from a table and shift the data, one row at a time, into a set of variables, ready for use?

And cycle thru a loop?

Thanks

What do you want to do with the data in the variables ?

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

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-12 : 08:38:12

THANKS EVERYONE!!!! LOOKS GOOD!!

I was about to try and declare a CURSOR. Do I need that? Tell me. It looks hopefully not. ID column looks great.

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-12 : 08:43:58

What I'm doing with all the variables, KHTAN, is all done and tested. It's a cool HTML report using xp_smtp_sendmail. After 8 years using Crystal Reports, this boss is saying no to that - it has to be "reliable" SQL which is OK. Only used loops in C++, not T-SQL. Scared.

There are binaries in there to fetch out (BLOBS). One is the email address. The rest is just junk data, all formatted right. Gotta get this loop done right. Might be more questions...

I had the ID column in my head. I'm liking it

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 08:45:04

I don't think you actually need the MAX thingy

SELECT	@i = MIN(ID)
FROM	YourTable

WHILE @i IS NOT NULL
	BEGIN
		-- Do processing here
		. . . 

		SELECT	@i = MIN(ID)
		FROM	YourTable
		WHERE	ID > @i
	END

This is just in case someone inserts a new record in YourTable while you iterates all records.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 08:46:47

quote:
Originally posted by mikebird

I had the ID column in my head. I'm liking it

Make the ID column an IDENTITY column and you're set!

E 12°55'05.25"
N 56°04'39.16"

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-12 : 08:51:27

How do I add an identity column to a temp table which is generated by a statement:

SELECT col1, col2... INTO <newtablename> FROM xyz...

I then want to pop the ID column in at the start and tried making it newid() but how do I really do it? I love this because I don't define the table - it's defined by my selection, I think. I get the error:

Server: Msg 8155, Level 16, State 1, Line 2
No column was specified for column 1 of '#validSOR'.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-12 : 08:58:18

Use identity function

SELECT IDENTITY(int, 1,1) AS [ID], col1, col2... INTO <newtablename> FROM xyz...

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-12 : 09:02:28

Got me, harsh. Thanks

I was at setting SELECT x = (newid()) but didn't want the uniqueidentifier in there! Using yours

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-12 : 09:08:32

Ahhhhhhh but NO! I had a lovely distinct query on that #table. Now it's not distinct any more.

Erase comment. I'll select the other columns and distinct those...

Now...

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-12 : 10:31:30

in the bit that Peso described, between the BEGIN and END section, waht I want is something to point at a certain row of my table.

I'm SELECTING those values into variables and and doing the processing. At the END, or somewhere, I want to step to the next row and do it all aagain. OBVIOUSLY

But what's the bit which points at the row? Is it a CURSOR? I thought I could declare a cursor with a name FOR <thetable> because when I see examples after the FOR comes a SELECT statement

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 10:34:25

[code]-- Make a placeholder for current row identifier
DECLARE @i INT

-- Select least row identifier
SELECT @i = MIN(ID)
FROM YourTable

-- As long a @i has a value, iterate all records
WHILE @i IS NOT NULL
BEGIN
-- Do processing here, what ever kind it mtight be
. . .

-- Get next least row identifier, larger than current
SELECT @i = MIN(ID)
FROM YourTable
WHERE ID > @i
END[/code]

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-12 : 10:38:12

The method which myself and Peter shown is not cursor, it is an alternative to CURSOR because generally you should avoid using cursors unless absolutely necessary, due to performance reasons.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-12 : 10:52:52

What does the WHILE do? Does it keep track of the count between the BEGIN & END? Otherwise I need to do

SET @i = @i +1

just before the END

Also, the whole process I run in between, using the variable setting for one row, all ends with a GO. The last thing in that script when it was run by itself. Now I'm stepping through it, do I put the END before the GO so it stacks up the iterations and finally runs them at the GO, when finished (sounds wrong) or do I put the END after the GO, so it keeps doing the job on the GO, then comes round again to the END to get the next row...?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 10:57:30

NO!
If there is a record missing/deleted, you will get an invalid entry as row identifier!

E 12°55'05.25"
N 56°04'39.16"

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-12 : 11:05:13

So you're saying I should delete rows once I've processed them? While on the current one and finished, delete?

Then the next will be the MIN(ID) again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 11:33:08

NO! Delete nothing from the YourTable table during execution!

Leave the code as is. The algorithm we provided is a failsafe method to accomplish your task.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 11:35:30

[code]DECLARE @i INT

SELECT @i = MIN(ID)
FROM YourTable

declare @column1 int,
@column2 varchar(20)

WHILE @i IS NOT NULL
BEGIN
SELECT @column1 = column1,
@column2 = column2
FROM YourTable
WHERE ID = @i

-- Do processing here, what ever kind it might be
/*
EXEC SomeOtherSP @Column1, @Column2
*/

SELECT @i = MIN(ID)
FROM YourTable
WHERE ID > @i
END[/code]

E 12°55'05.25"
N 56°04'39.16"

X002548
Not Just a Number

15586 Posts

Posted - 2008-03-12 : 11:42:15

It would be much more helpful if you describe what you are actually trying to accomplish

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources