Use Column Names in a loop in sp

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

Transact-SQL (2005)

Use Column Names in a loop in sp

Author

Topic

GregDDDD
Posting Yak Master

120 Posts

Posted - 2010-03-05 : 19:59:42

I have a good grasp of SQL but I am new to stored procedures. I have a situation where tables that can have multiple rows that group on 2 or 3 columns, but only one of those rows should ever have record_active = True. I can easily write the stored procedure to find the rows with a query like this...


SELECT SELECT member_id, item_id, Count(item_id) AS ItemCount
FROM dbo.tbl_text
WHERE record_active = 'True'
GROUP BY member_id, item_id
HAVING Count(item_id) > 1

How can I use the columns member_id and item_id in the same sp to now get all of the rows in a single recordset. The idea is that I would sort them by create_date ASC and reset all but the last one to record_active = 'False'. I can write the SQL fine, but I don't know how to concatenate the new SQL string using the values from member_id and item_id.

Ideally, I would move through the results returned from above and create queries like this...


SELECT record_active
FROM dbo.tbl_text
WHERE member_id = ' + ???? + '
AND item_id = ????
ORDER BY create_date

I would move through these, one for each row from the first query, and set all but one record_active = False. I need to replace the question marks with values from the first query. I could do this in 2 minutes in ADO or DAO. I am stumped working in SQL Server 2005.
Greg

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-03-05 : 23:52:37

what about this ....

with temp as
(
SELECT member_id, item_id, Count(item_id) AS ItemCount
FROM dbo.tbl_text
WHERE record_active = 'True'
GROUP BY member_id, item_id
HAVING Count(item_id) > 1
)
select m.record_active, t.* from temp t
cross apply
dbo.tbl_text m
WHERE m.member_id = t.member_id
AND m.item_id = t.item_id
ORDER BY create_date

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-06 : 02:06:36

you can do it with single statement as


UPDATE t
SET t.record_active = 'False'
FROM (SELECT record_active,
      COUNT(CASE WHEN record_active='True' THEN 1 ELSE NULL END) OVER (PARTITION BY member_id, item_id) AS GrpCnt,
      ROW_NUMBER() OVER (PARTITION BY member_id, item_id ORDER BY create_date DESC) AS Seq
      FROM YourTable)t
WHERE t.GrpCnt > 1
AND Seq > 1
AND record_active='True'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GregDDDD
Posting Yak Master

120 Posts

Posted - 2010-03-06 : 13:13:05

Wow. There is a lot I don't know about stored procedures, and I could maybe say that about SQL in general, as well. I have never seen OVER and PARTITION BY, nor am I familiar with ROW_NUMBER() OVER.

visakh16,

That code is very attractive, but not being familiar with some of the phrasing, would it always leave the most recent row with record_active = True?

Greg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-07 : 02:28:40

quote:
Originally posted by GregDDDD

Wow. There is a lot I don't know about stored procedures, and I could maybe say that about SQL in general, as well. I have never seen OVER and PARTITION BY, nor am I familiar with ROW_NUMBER() OVER.

visakh16,

That code is very attractive, but not being familiar with some of the phrasing, would it always leave the most recent row with record_active = True?

Greg

it will leave out recent row always and look for others in the each group of member_id, item_id and for each one with record_active='True' it updates it to record_active='False'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources