How to assign MAX(ROW_NUMBER) to a variable

[jtertin]

I would think this would be straight forward:

DECLARE @TotalRows INT

With Temp AS (SELECT ROW_NUMBER() OVER(ORDER BY rID) AS RowNumber
FROM tblData
WHERE intSortNumber=115
AND intBatch=321)
SET @TotalRows=(SELECT MAX(RowNumber) FROM Temp)

Unfortunately, it returns the error "Incorrect syntax near the keyword
'with'. If this statement is a common table expression or an
xmlnamespaces clause, the previous statement must be terminated with a
semicolon." which appears to be related to the variable declaration.
If I put a semicolon after the DECLARE statement, I get the [very
ambiguous] error "Incorrect syntax near the keyword 'SET'." instead.

I need this number so I can use it as the number of iterations (and
"pointer") for a loop to run through elsewhere in the stored
procedure.

Looking for another point of view.... thanks!

[Erland Sommarskog]

jtertin (jte...@gmail.com) writes:
> I would think this would be straight forward:
>
> DECLARE @TotalRows INT
>
> With Temp AS (SELECT ROW_NUMBER() OVER(ORDER BY rID) AS RowNumber
> FROM tblData
> WHERE intSortNumber=115
> AND intBatch=321)
> SET @TotalRows=(SELECT MAX(RowNumber) FROM Temp)
>
> Unfortunately, it returns the error "Incorrect syntax near the keyword
> 'with'. If this statement is a common table expression or an
> xmlnamespaces clause, the previous statement must be terminated with a
> semicolon." which appears to be related to the variable declaration.
> If I put a semicolon after the DECLARE statement, I get the [very
> ambiguous] error "Incorrect syntax near the keyword 'SET'." instead.

DECLARE @TotalRows INT;

With Temp AS (SELECT ROW_NUMBER() OVER(ORDER BY rID) AS RowNumber
FROM tblData
WHERE intSortNumber=115
AND intBatch=321)

SELECT @TotalRows= MAX(RowNumber) FROM Temp

SET is pretty useless for variable assignment in my opinion. You can
only assign one variable at a time, and as you have noticed, it
does not work when you use a CTE. (Well, I haven't tried all
permutaions, but I'm going to.)

Looking at you query as posted, it seems that

SELECT COUNT(*) FROM tblData WHERE intSortNumber=115 and intBatch321

would yield the same result.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[jtertin]

Thank you for your feedback. That should work.

The count method will not work since I do not only want to know the
number of rows, but also need a baseline to use for a loop within a
stored procedure. With this method, I can use the RowNumber column as
a basis for my loop.

Thank you very much!

[Michael Coles]

For the code sample you provided you should get the same results using a MAX
on the ROW_NUMBER in a CTE and the COUNT(*) as Erland indicated. You also
eliminate the sorts required to generate the ROW_NUMBER. Not sure what else
you're doing with ROW_NUMBER in the code you didn't post, but what Erland
posted should return equivalent results to what you posted. The ROW_NUMBER
result in your CTE only exists for that one query in the example your
provided, by the way.

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

"jtertin" <jte...@gmail.com> wrote in message
news:0d81b747-ac40-4387...@m25g2000yqc.googlegroups.com...

[jtertin]

I was trying to keep my question as succinct as possible.

If you would like further explanation, my stored procedure works as
follows:

1) Count all applicable rows.
2) Select range and include Row_Number
3) Using a while loop ('i'<=Count) against the Row_Number returned in
the results in order to fulfill the end goal.

I ended up expanding on the above and created a temporary table
including a Row_Number column in order to minimizing SP run time.

Hope this clears things up. I wasn't trying to short anyone with
information, but including the entire SP would have just muddied the
water but did indicate "I need this number so I can use it as the

number of iterations (and "pointer") for a loop to run through

elsewhere in the stored procedure." to convey the above.

[Michael Coles]

"jtertin" <jte...@gmail.com> wrote in message

news:00cbb390-f300-4dd9...@m25g2000yqc.googlegroups.com...

It always helps to have as complete and accurate information as possible.

BTW, you may be able to optimize your code further by eliminating the loop
and procedural code; however, only you can determine that at this point.

[--CELKO--]

Since what you have described is the worst possible way to ever write
SQL, you might want to post the procedure so that someone can show you
how to program in a declarative language.

You are mimicking a magnetic tape file or deck of punch cards from the
1950's. Your temp table sounds like the scratch tape logic that
dominated programming in those days. These nightmares can often be
replaced with one proper set-oriented statement and will run 2-3
orders of magnitude faster.

[Carl Rayer]

Hi

The following is possible:

declare @Xml xml
SET @Xml = (SELECT * FROM sysobjects FOR XML AUTO)
select @XML

But attempting to achieve the what seems to be the same via a CTE is not:

declare @Xml xml;
SET @Xml = (
WITH CTE_Sysobjects (name, id)
AS
(
SELECT name, id FROM sysobjects
)
SELECT
[name], [id ]
FROM CTE_Sysobjects
FOR XML AUTO
)
select @XML

There are actually some good reasons for trying to extract the XML in this way (to do with maintaining interfaces), but short of using user-defined functions, temporary tables, inter alia, can a CTE assign into an XML variable?

TIA

Carl

>> On Wednesday, December 09, 2009 6:53 PM Erland Sommarskog wrote:

>> jtertin (jte...@gmail.com) writes:
>>
>> DECLARE @TotalRows INT;
>>
>> With Temp AS (SELECT ROW_NUMBER() OVER(ORDER BY rID) AS RowNumber
>> FROM tblData
>> WHERE intSortNumber=115
>> AND intBatch=321)

>> SELECT @TotalRows= MAX(RowNumber) FROM Temp
>>
>> SET is pretty useless for variable assignment in my opinion. You can
>> only assign one variable at a time, and as you have noticed, it

>> does not work when you use a CTE. (Well, I have not tried all
>> permutaions, but I am going to.)

>>
>> Looking at you query as posted, it seems that
>>
>> SELECT COUNT(*) FROM tblData WHERE intSortNumber=115 and intBatch321
>>
>> would yield the same result.
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>>
>> Links for SQL Server Books Online:
>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

>>> On Thursday, December 10, 2009 9:04 AM jtertin wrote:

>>> Thank you for your feedback. That should work.
>>>
>>> The count method will not work since I do not only want to know the
>>> number of rows, but also need a baseline to use for a loop within a
>>> stored procedure. With this method, I can use the RowNumber column as
>>> a basis for my loop.
>>>
>>> Thank you very much!

>>>> On Thursday, December 10, 2009 7:32 PM Michael Coles wrote:

>>>> For the code sample you provided you should get the same results using a MAX
>>>> on the ROW_NUMBER in a CTE and the COUNT(*) as Erland indicated. You also
>>>> eliminate the sorts required to generate the ROW_NUMBER. Not sure what else

>>>> you are doing with ROW_NUMBER in the code you did not post, but what Erland

>>>> posted should return equivalent results to what you posted. The ROW_NUMBER
>>>> result in your CTE only exists for that one query in the example your
>>>> provided, by the way.
>>>>

>>>> --
>>>> Thanks
>>>>
>>>> Michael Coles
>>>> SQL Server MVP
>>>> Author, "Expert SQL Server 2008 Encryption"
>>>> (http://www.apress.com/book/view/1430224649)
>>>> ----------------

>>>>> On Thursday, December 10, 2009 10:33 PM jtertin wrote:

>>>>> I was trying to keep my question as succinct as possible.
>>>>>
>>>>> If you would like further explanation, my stored procedure works as
>>>>> follows:
>>>>>
>>>>> 1) Count all applicable rows.
>>>>> 2) Select range and include Row_Number
>>>>> 3) Using a while loop ('i'<=Count) against the Row_Number returned in
>>>>> the results in order to fulfill the end goal.
>>>>>
>>>>> I ended up expanding on the above and created a temporary table
>>>>> including a Row_Number column in order to minimizing SP run time.
>>>>>

>>>>> Hope this clears things up. I was not trying to short anyone with

>>>>> information, but including the entire SP would have just muddied the

>>>>> water but did indicate "I need this number so I can use it as the

>>>>> number of iterations (and "pointer") for a loop to run through

>>>>> elsewhere in the stored procedure." to convey the above.

>>>>>> On Friday, December 11, 2009 10:27 PM Michael Coles wrote:

>>>>>> It always helps to have as complete and accurate information as possible.
>>>>>>
>>>>>> BTW, you may be able to optimize your code further by eliminating the loop
>>>>>> and procedural code; however, only you can determine that at this point.
>>>>>>
>>>>>> --
>>>>>> Thanks
>>>>>>
>>>>>> Michael Coles
>>>>>> SQL Server MVP
>>>>>> Author, "Expert SQL Server 2008 Encryption"
>>>>>> (http://www.apress.com/book/view/1430224649)
>>>>>> ----------------

>>>>>>> Submitted via EggHeadCafe - Software Developer Portal of Choice
>>>>>>> Simple .NET HEX PixelColor Utility
>>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/5617a491-963d-4510-b8f1-1863ddf52bc1/simple-net-hex-pixelcolor-utility.aspx

[Tom Cooper]

The WITH <ctename> must be the first part of the query, and you cannot use a
SET with a cte, but you can do

declare @Xml xml;

WITH CTE_Sysobjects (name, id)
AS
(
SELECT name, id FROM sysobjects
)

SELECT @Xml = (

SELECT
[name], [id ]
FROM CTE_Sysobjects
FOR XML AUTO
)
select @XML

Tom

"Carl Rayer" <carl....@gmail.com> wrote in message
news:201098124...@eggheadcafe.com...