How to set variable to field in current row

[jimoo]

I am trying to set a couple of variables to equal the field values of the current record so I can process that information. I put together sample code that creates a temp table and has 2 attributes (animal & breed) and I would like to set the variables myAnimal and myBreed accordingly based on row number and the display them.

Can someone assist assigning and displaying the variables?

create table #myTable (animal varchar(10), type varchar(10))
insert into #myTable values ('Dog','Pug')
insert into #myTable values ('Cat','Siamese')
insert into #myTable values ('Bird','Robin')


DECLARE @Counter INT 
SET @Counter=1

DECLARE @myAnimal as VARCHAR(10)
DECLARE @mybreed as VARCHAR(10)


WHILE ( @Counter <= 3)
BEGIN
   -- Would like to set @myAnimail variable with current record.  The first would be dog, next cat, next Bird
   -- Would like to set @@mybreed variable with current record.  The first would be pug, next Siamese, next Robin

   -- Then display the variables (@myAnimails & myBreed)

	Print 'Number ' + CAST(@Counter AS NVARCHAR(100))
    SET @Counter  = @Counter  + 1
END

drop table #myTable

Jim

 

[jimoo]

I found a crude way of assigning the variable to an attribute on the current records, but it's not very clean. It required me to select all the data into a new table (temp) and include a row number using the ROW_NUMBER function. Since my data table is < 500 records and it is already sorted it works okay and there isn't a lot of overhead, but a better way would be to spin through the current table and pick up the data values.

In short, I have kludgie work around, but it's not the best solution so feel free to assist if you can make it work without me having to select the data into a new table.

 create table #myTable (animal varchar(10), breed varchar(10))
insert into #myTable values ('Dog','Pug')
insert into #myTable values ('Cat','Siamese')
insert into #myTable values ('Bird','Robin')

select ROW_NUMBER() OVER(ORDER BY animal ASC) as RowNumber, animal, breed INTO #TempData from #myTable

DECLARE @Counter INT 
SET @Counter=1

DECLARE @myAnimal as VARCHAR(10)
DECLARE @mybreed as VARCHAR(10)


WHILE ( @Counter <= 3)
BEGIN
   -- Would like to set @MyAnimail variable with current record.  The first would be dog, next cat, next Bird
   -- Would like to set @@mybreed variable with current record.  The first would be pug, next Siamese, next Robin

   -- Then display the variables

	SELECT @myAnimal = animal FROM #TempData WHERE RowNumber = @Counter
	SELECT @myBreed = breed FROM #TempData WHERE RowNumber = @Counter

	Print @myAnimal + ', ' + @myBreed
    SET @Counter  = @Counter  + 1
END

drop table #myTable
drop table #TempData

Jim

 

[Chris Miller]

To make it short, add an integer identity(1,1) column to your temp table #myTable and you get a generated 'row number' in order of inserts, that doesn't need the use of row_nuber() and the second temp table.

Better use a table variable, like this:

Declare @myTable as Table (id integer identity(1,1), animal varchar(10), breed varchar(10));

insert into @myTable values ('Dog','Pug') , ('Cat','Siamese') , ('Bird','Robin');


DECLARE @Counter as INT;
SET @Counter=1

DECLARE @myAnimal as VARCHAR(10), @mybreed as VARCHAR(10)

WHILE ( @Counter <= 3)
BEGIN
   -- Would like to set @MyAnimail variable with current record.  The first would be dog, next cat, next Bird
   -- Would like to set @@mybreed variable with current record.  The first would be pug, next Siamese, next Robin

   SELECT @myAnimal = animal, @myBreed = breed FROM @myTable WHERE Id  = @Counter

   Print @myAnimal + ', ' + @myBreed;

   SET @Counter  = @Counter  + 1
END

-- and by the way:
-- Select animal+', '+breed from @myTable order by id;
-- will create that output as a T-SQL result, not just PRINT output.
-- Assuming you want to do something for each record, ie call a stored proc, this would be OK to do, also if @myTable is a normal table from which you select records to process.

Just for your info, if you're interested in the details:

SQL Server has no concept of a tables record number or current record, not even internally, like many SQL Servers it organizes data in pages and a record is not bound to its first physical location forever, as pages are organized by a) a clustered index, meant to always keep all data in sort order by that index, getting fast read results for the price of inserts rearranging pages and splitting them up in case a new record needs to be sorted into an already full page. b) statistics about tables might also lead to page rearrangements.

So there is no physical location that's constant like an identity column can be (as it's a read-only column type, only set once at insert (with exceptions, but let's not drill down that deep)). There is %%physloc%%, but that's an internal column not necessarily staying as-is for a record.

This all is rather a legacy behavior for the good old platter hard drives, to have as fewest as possible read/write head movements. With SSDs this data organization plays a minor role (it's still a bit faster to fetch SSD NVRAM in block sizes, but SSD can pretty much access data at the same speed, no matter if it's all concentrated in a small address range (which is the equivalent of successive blocks in a block device like platter HDDs are) or if the data is scattered all around in the NVRAM.

As we're in this age of SSDs, it actually shouldn't matter that you need to fetch every single record in your loop. And even when using classic HDDs SQL Server works with caching, also in-memory caching, anyway.

Last not least, the only way to get a temporary row number is by query, and then it's only valid for that result in the order fetched by your ORBER BY clause or 'random'. It's not that you really get random results, T-SQL will go through pages, but for long-term you can't expect the first record of a simple SELECT * FROM TABLE to always stay the first inserted record. When you want chronological order also better not rely on integer identity sequence, add a datetime field and store the insert time, then sort by that column.

Besides Row_Number() you can also fetch data into a cursor and then fetch single row by row, but at the end that's what languages like PHP do when you give them a result with many rows, they can decide to fetch row by row or all data, you don't loop through records in SQL Server unless you have to call a stored procedure or function for every single row. Even that can be put into a query, though, as you can use expressions for fields in the field list and call table-valued functions in the table clause of a query, ie join the result of a table-valued function to other tables or further table-valued function results.

Chriss