Unable to print variables outside of loop

Question

Unable to print variables outside of loop

jshurak

SSCrazy

Points: 2763
More actions
September 19, 2011 at 12:24 pm

#100419

I'm getting a little hung up on this code for some reason. The Print statements inside the while clauses will successfully print, however, the final two Print statements do not prin successfully. Any thoughts?
WHILE @server_cnt > 0
BEGIN
DELETE FROM #TMP_server where servername = @server
SET @server = (select top 1 servername from #tmp_server)
SET @server_out = @server_out + ', ' + @server
SET @server_cnt = @server_cnt - 1
PRINT @server_out
END
WHILE @db_cnt > 0
BEGIN
DELETE FROM #TMP_db where databasename = @db
SET @db = (select top 1 databasename from #tmp_db)
SET @db_out = @db_out + ', ' + @db
SET @db_cnt = @db_cnt - 1
PRINT @db_out
END
PRINT @server_out
PRINT @db_out

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

any chance the values could be null? it looks to me like your code might do a SELECT TOP 1 from the table , and that could reutrn NULL, but still be in your loop.

change it to this to double check:

PRINT 'ServerOut: ' + ISNULL(@server_out,'NULL!')

PRINT 'DatabaseOut: ' + ISNULL(@db_out,'NULL!')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jshurak SSCrazy Points: 2763 More actions · Answer 2

jshurak

SSCrazy

Points: 2763

September 19, 2011 at 2:05 pm

#1383478

hmmmm, I see. I will try.

LutzM SSC Guru Points: 107049 More actions · Answer 3

The order used to process the data leads to a NULL value at the last pass (after delete of the last row SELECT @db will return NULL. When you concatenate a string and NULL you'll get a NULL value being represented as an empty string when using PRINT. If you change it to SELECT I'd expect to see a NULL value as the last value for each loop as well as for the final output.

A slightly off-topic question: what is the exact purpose of this loop? There might be a set based solution...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

jshurak SSCrazy Points: 2763 More actions · Answer 4

I got you guys, that was it. The order of the delete statement was off and it should have occurred after the concatenation.

This is part of a database monitoring system being implemented. Its all SQL Server driven. The latest being (somewhat) real time server and database availability. This particular job compares a database availability table (updated in 15 minute intervals) to look up tables to find missing servers or offline databases. This particular job step concatenates these servers and db's together to send them out to pagers/text messages, emails, etc..

If you can suggest a set based solution, I'm all ears.

gofrancesc Hall of Fame Points: 3269 More actions · Answer 5

Hello,

try it:

DECLARE @tmp_server TABLE (servername Varchar(10))

INSERT INTO @tmp_server

SELECT 'a' UNION SELECT 'b' UNION SELECT 'c' UNION

SELECT 'd' UNION SELECT 'e' UNION SELECT 'f'

DECLARE @server_out AS VARCHAR(100)

SET @server_out = ''

SELECT @server_out = @server_out + servername + ', ' FROM @tmp_server

-- Final result

select LEFT(@server_out, LEN(@server_out) - 1)

Francesc