Loop through records - 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

Loop through records

Author

Topic

Saab2005
Starting Member

4 Posts

Posted - 2011-12-16 : 13:56:48

Hi,

I have a table something like the picture below.

I want to loop throug the columns for each row and populate the ERROR_MESSAGE field based on the loop result.

can this be done?

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-16 : 14:13:58

Sure, but we need more info on this part to help you: populate the ERROR_MESSAGE field based on the loop result. We need to know what the logic is in order to help. The reason why we need more info is because it's best to do things in sets and not in loops in SQL Server. Usually we can solve a problem with one query where we process an entire set.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-16 : 14:32:08

your link is broken here

Can you post your question in the form of a BRD

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

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-16 : 14:35:06

Works fine for me.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-16 : 14:41:52

QualComm is less serious about security I'm guessing ;-)

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

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-16 : 14:59:13

Not true, they just protect us from unsecure stuff. Prudential goes way too far with blocking sites. I mean, wasn't SQLTeam.com blocked for a bit by them?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-16 : 15:09:12

They use a thrird party...not sure..

So when I went ballistic when the blocked SQLTeam and dbForums I sent in a request and got them unblocked

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

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Saab2005
Starting Member

4 Posts

Posted - 2011-12-16 : 15:36:08

quote:
Originally posted by tkizer

Sure, but we need more info on this part to help you: populate the ERROR_MESSAGE field based on the loop result. We need to know what the logic is in order to help. The reason why we need more info is because it's best to do things in sets and not in loops in SQL Server. Usually we can solve a problem with one query where we process an entire set.

OK, lets put it this way...

I have a table with 4 fields (COL_1, COL_2, COL_3, COL_4).The values in these fields can be 'OK' or 'ERROR'.

I want to add another field into this table (Error_Message) and populate this field by checking the values of the other fields.

if any of the COL_1, COL_2, COL_3, COL_4 has 'Error' then i want to populate the Error_Message field with a message like "Error - Column header(s)" else "No Error".

Hope I made it little clear.

Saab2005
Starting Member

4 Posts

Posted - 2011-12-18 : 05:51:27

bump!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-18 : 07:15:59

This is one of those cases where brute force (aka simple-minded) approach is probably the simplest. And, here is my brute force approach to it.

SELECT 
   CASE 
      WHEN Errors = '' THEN 'NO ERROR'
      ELSE 'ERROR - ' + Errors
   END
FROM
(   SELECT
         CASE WHEN col1 <> 'ERROR' THEN '' ELSE 'col1,' END +
         CASE WHEN col2 <> 'ERROR' THEN '' ELSE 'col2,' END +
         CASE WHEN col2 <> 'ERROR' THEN '' ELSE 'col2,' END AS Errors
   FROM
      YourTable
)T;

Saab2005
Starting Member

4 Posts

Posted - 2011-12-23 : 12:15:50

this done the trick...

UPDATE TEST_ERR_MSG
SET ERROR_MESSAGE = CASE
WHEN(
		CASE WHEN COL_1 <> 'ERROR' THEN '' ELSE 'COL_1,' END+
		CASE WHEN COL_2 <> 'ERROR' THEN '' ELSE 'COL_2,' END+
		CASE WHEN COL_3 <> 'ERROR' THEN '' ELSE 'COL_3,' END+
		CASE WHEN COL_4 <> 'ERROR' THEN '' ELSE 'COL_4,' END
	) <> '' THEN 'ERROR - ' +
	(
		CASE WHEN COL_1 <> 'ERROR' THEN '' ELSE 'COL_1,' END+
		CASE WHEN COL_2 <> 'ERROR' THEN '' ELSE 'COL_2,' END+
		CASE WHEN COL_3 <> 'ERROR' THEN '' ELSE 'COL_3,' END+
		CASE WHEN COL_4 <> 'ERROR' THEN '' ELSE 'COL_4,' END
	)ELSE 'NO ERROR' END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources