Assign a row to variable - SQL Server Forums

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

Assign a row to variable

Author

Topic

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-12-10 : 06:21:10

This code prints the last row from the query. How do you set the variables to the first row? I will need to be able to advance to the next row after running a script on the first row. (or remove the (first or last) row and run the script on the new row.)

declare
@dbname nvarchar(50),
@row int,
@server varchar(22)

SELECT @server = [Server], @dbname = [Database] from dbo.BackupHistory
where convert(varchar(11),backup_finish ,101) = Convert(char,Dateadd(d,-1,getdate()),101)
order by [size] desc

print @server
print @dbname

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-10 : 06:22:36

use

SELECT TOP 1 @Server...

E 12°55'05.63"
N 56°04'39.26"

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-12-10 : 06:27:20

quote:
Originally posted by Peso

use

SELECT TOP 1 @Server...

great, how do I advance to the next row? can I use @row as a variable and just do a +1? if so How do I use the variable in the sequence?

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-10 : 06:35:25

[code]-- Solution 1
SELECT [Server],
[Database]
FROM dbo.BackupHistory
WHERE DATEDIFF(DAY, backup_finish, GETDATE()) = 1
ORDER BY [size] DESC

-- Solution 2
CREATE TABLE #Backup
(
rowID INT IDENTITY(1, 1) PRIMARY KEY,
[Server] SYSNAME,
[Database] SYSNAME
)

INSERT #Backup
(
[Server],
[Database]
)
SELECT [Server],
[Database]
FROM dbo.BackupHistory
WHERE DATEDIFF(DAY, backup_finish, GETDATE()) = 1
ORDER BY [size] DESC

DECLARE @currID INT,
@Server SYSNAME,
@Database SYSNAME

SET @currID = 1

WHILE @currID IS NOT NULL
BEGIN
SELECT @Server = [Server],
@Database = [Database]
FROM #Backup
WHERE rowID = @currID

PRINT @Server
PRINT @Database

SELECT @currID = MIN(rowID)
FROM #Backup
WHERE rowID > @currID
END

DROP TABLE #Backup[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 09:17:33

quote:
Originally posted by GRAYWOLF

quote:
Originally posted by Peso

use

SELECT TOP 1 @Server...

great, how do I advance to the next row? can I use @row as a variable and just do a +1? if so How do I use the variable in the sequence?

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

can you explain your actual scenario? i think it would much easier for you to use set based solution. any reason why you want to do row by row processing?

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-12-10 : 13:58:17

I am trying to run restores of backups to test the validity of the backup.

What I am trying to do with this segment is pull all of the backup files (sorted by size of the file). I have another table that will keep track of what DBs have been restored (as to not repeat). I will compare the row to the table and if it has been done I will go to the next row. When I reach a row that has not been restored, I will run a restore.

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 21:08:32

as peso suggested first make temp table


CREATE TABLE	#Backup
		(
			rowID INT IDENTITY(1, 1) PRIMARY KEY,
			[Server] SYSNAME,
			[Database] SYSNAME
		)

INSERT		#Backup
		(
			[Server],
			[Database]
		)
SELECT		[Server],
		[Database]
FROM		dbo.BackupHistory
WHERE		DATEDIFF(DAY, backup_finish, GETDATE()) = 1
ORDER BY	[size] DESC

then left join with your table and get ones which dont exist

SELECT t.*
FROM #Temp t
LEFT JOIN YourTable t1
ON t.Server=t1.Server
AND t.Database=t1.Database
WHERE t1.Server IS NULL

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-12-11 : 00:50:09

This works and prints out a table with 279 records. I try to insert the following select statement before the Drop Table, but it never gives me anything other than the 279 records:

SELECT TOP 1 @server = [Server], @dbname = [Database] from #temptable
print @server
print @dbname

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-11 : 02:05:09

quote:
Originally posted by GRAYWOLF

This works and prints out a table with 279 records. I try to insert the following select statement before the Drop Table, but it never gives me anything other than the 279 records:

SELECT TOP 1 @server = [Server], @dbname = [Database] from #temptable
print @server
print @dbname

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

not sure how you got 279 records for above query. it returns only 1 record.

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-12-11 : 02:26:32

This code gives me 3 columns and 279 rows...

CREATE TABLE #temptable
(
rowID INT IDENTITY(1, 1) PRIMARY KEY,
[Server] SYSNAME,
[Database] SYSNAME
)

INSERT #temptable
(
[Server],
[Database]
)
SELECT [Server],
[Database]
FROM dbo.BackupHistory
WHERE DATEDIFF(DAY, backup_finish, GETDATE()) = 1
ORDER BY [size] DESC

SELECT #temptable.*
FROM #temptable
LEFT JOIN dbo.Restores
ON #temptable.[Server]=dbo.Restores.[Server]
AND #temptable.[Database]=dbo.Restores.[Database]
WHERE dbo.Restores.Server IS NULL

--SELECT TOP 1 @server = [Server], @dbname = [Database] from #temptable

DROP TABLE #temptable

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-11 : 02:40:59

ok...so what was the problem then? wasnt this what you wanted?

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-12-11 : 02:52:14

partially, yes. I need to grab the server and db from the first row and pass it to a restore, then take the next row and do the same.

The problem I am having right now is when I include print for both @server and @dbname It doesn't print it...all I get is the fill list of 279 rows. This may be assigning the right value to the variable, but it is not printing it out so i can verify it.

I tried it both ways:

DROP TABLE #temptable
print @server
print @dbname

print @server
print @dbname
DROP TABLE #temptable

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-11 : 02:58:49

are you doing print after select top 1 command?

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-12-11 : 02:59:21

I don't see a print command for the temporary table. How do I get that to not print? It is creating a table with the proper info.

Eventually, I intend to make this a function that repeats a certain number of times each day to kick off the test restores. Once it kicks off a restore I will have in insert that info to the Restores table and then rerun this whole thing to create a fresh table. Then I only need the first row of info to pass to the restore.

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-12-11 : 03:00:48

Yes, I am using the print commands just like I posted. That is the same drop command from the larger script above.

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-12-11 : 06:05:52

OK, it is working now...I started over and it now works....

Thanks!

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-11 : 09:48:08

cool

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources