Sql Server select and update X rows

Question

I'm using Sql Server 2012.

I need to select rows from a table for processing. The number of rows needs to be variable. I need to update the rows I'm selecting to a "being processed" status - I have a guid to populate for this purpose.

I've encountered several examples of using row_number() and a couple of examples of ways of using CTE's, but I'm not sure on how to combine them (or if that's even the correct strategy). I would appreciate any insight.

Here is what I have so far:

DECLARE @SessionGuid uniqueidentifier, @rowcount bigint
SELECT @rowcount = 1000
SELECT @sessionguid = newid()
 DECLARE @myProductChanges table (
 ProductChangeId bigint
 , ProductTypeId smallint
 , SourceSystemId tinyint
 , ChangeTypeId tinyint );
 WITH NextPage AS 
 (
 SELECT 
 ProductChangeId, ServiceSessionGuid,
 ROW_NUMBER() OVER (ORDER BY ProductChangeId) AS 'RowNum' 
 FROM dbo.ProductChange
 WHERE 'RowNum' < @rowcount
 )
 UPDATE dbo.ProductChange
 SET ServiceSessionGuid = @sessionguid, ProcessingStateId = 2, UpdatedDate = getdate()
 OUTPUT
 INSERTED.ProductChangeId,
 INSERTED.ProductTypeId,
 INSERTED.SourceSystemId,
 INSERTED.ChangeTypeId
 INTO @myProductChanges
 FROM dbo.ProductChange as pc join NextPage on pc.ProductChangeId = NextPage.ProductChangeId

From here I will select from my temp table and return the data:

SELECT mpc.ProductChangeId
 , pt.ProductName as ProductType
 , ss.Name as SourceSystem
 , ct.ChangeDescription as ChangeType
FROM @myProductChanges as mpc 
 join dbo.R_ProductType pt on mpc.ProductTypeId = pt.ProductTypeId
 join dbo.R_SourceSystem ss on mpc.SourceSystemId = ss.SourceSystemId
 join dbo.R_ChangeType ct on mpc.ChangeTypeId = ct.ChangeTypeId
ORDER BY ProductType asc

So far this doesn't work for me. I get an error when I try to run it:

Msg 8114, Level 16, State 5, Line 20
Error converting data type varchar to bigint.

I'm not clear on what I'm doing wrong - so - any help is appreciated.

Thanks!

BTW, here are some of the questions I've used as reference to try and solve this:

https://stackoverflow.com/questions/9777178

https://stackoverflow.com/questions/3319842

https://stackoverflow.com/questions/6402103

Answer 1

This subquery makes no sense:

 SELECT 
 ProductChangeId, ServiceSessionGuid,
 ROW_NUMBER() OVER (ORDER BY ProductChangeId) AS 'RowNum' 
 FROM dbo.ProductChange
 WHERE 'RowNum' < @rowcount

You can't reference the alias RowNum at the same scope (and you are trying to compare a string, not an alias, anyway), because when the WHERE clause is parsed, the SELECT list hasn't been materialized yet. What you need is either another nest:

SELECT ProductChangeId, ServiceSessionGuid, RowNum
FROM (SELECT ProductChangeId, ServiceSessionGuid, 
 ROW_NUMBER() OVER (ORDER BY ProductChangeId) AS RowNum
 FROM dbo.ProductChange
) AS x WHERE RowNum < @rowcount

Or:

SELECT TOP (@rowcount-1) ProductChangeId, ServiceSessionGuid, 
 ROW_NUMBER() OVER (ORDER BY ProductChangeId) AS RowNum
FROM dbo.ProductChange
ORDER BY ProductChangeId

Also please stop using 'alias' - when you need to delimit aliases (you don't in this case), use [square brackets].

• trenthaynes: So my error is related to the comparison of 'RowNum' (as a string value and not an alias) to @rowcount - that explains a lot. I borrowed from an example I found in an attempt to make this work.
• trenthaynes: In the second example (SELECT TOP...), do I still need ROW_NUMBER() in order to make sure that I get the next 'N' rows ordered by ProductChangeId column?
• Aaron Bertrand: No, TOP will limit to that number of rows, but I wasn't sure if you needed the row_number() for other reasons...

Answer 2

I'm guessing, but I think you want <= rather than < if you want to affect @rowcount rows, not one less.

Another tip is that CTEs can be updated directly*, as shown here:

WITH NextPage AS 
(
 SELECT TOP(@rowcount) * 
 FROM dbo.ProductChange
)
UPDATE NextPage
SET ServiceSessionGuid = @sessionguid, ProcessingStateId = 2, UpdatedDate = getdate()
OUTPUT
 INSERTED.ProductChangeId,
 INSERTED.ProductTypeId,
 INSERTED.SourceSystemId,
 INSERTED.ChangeTypeId
INTO @myProductChanges

* The updates affect the base table in the CTE, i.e. dbo.ProductChange

• trenthaynes: Thanks for pointing that out. I do want to make my code (even SQL) as succinct and efficient as I can. And yes, I did actually want <=, point taken.