 |
|
|
|
Next: text, ntext, and image data types cannot be compa..
|
| Author |
Message |
External
Since: Nov 06, 2008
Posts: 4
|
(Msg. 1) Posted: Wed Dec 09, 2009 3:01 pm
Post subject: How to assign MAX(ROW_NUMBER) to a variable
Archived from groups: microsoft>public>sqlserver>programming (more info?)
|
|
|
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! |
|
| Back to top |
|
 | |
External
Since: May 30, 2004
Posts: 1649
|
(Msg. 2) Posted: Wed Dec 09, 2009 7:25 pm
Post subject: Re: How to assign MAX(ROW_NUMBER) to a variable [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
jtertin ( ) 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, esquel.DeleteThis@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 |
|
| Back to top |
|
| |
External
Since: Nov 06, 2008
Posts: 4
|
(Msg. 3) Posted: Thu Dec 10, 2009 5:38 am
Post subject: Re: How to assign MAX(ROW_NUMBER) to a variable [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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! >> Stay informed about: How to assign MAX(ROW_NUMBER) to a variable |
|
| Back to top |
|
| |
External
Since: Nov 06, 2008
Posts: 4
|
(Msg. 4) Posted: Thu Dec 10, 2009 7:03 pm
Post subject: Re: How to assign MAX(ROW_NUMBER) to a variable [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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. >> Stay informed about: How to assign MAX(ROW_NUMBER) to a variable |
|
| Back to top |
|
| |
External
Since: Dec 10, 2009
Posts: 2
|
(Msg. 5) Posted: Thu Dec 10, 2009 7:32 pm
Post subject: Re: How to assign MAX(ROW_NUMBER) to a variable [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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" wrote in message
> 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!
> >> Stay informed about: How to assign MAX(ROW_NUMBER) to a variable |
|
| Back to top |
|
| |
External
Since: Dec 10, 2009
Posts: 2
|
(Msg. 6) Posted: Fri Dec 11, 2009 10:27 pm
Post subject: Re: How to assign MAX(ROW_NUMBER) to a variable [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
"jtertin" wrote in message
>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.
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)
---------------- >> Stay informed about: How to assign MAX(ROW_NUMBER) to a variable |
|
| Back to top |
|
| |
External
Since: Apr 17, 2007
Posts: 417
|
(Msg. 7) Posted: Sun Dec 13, 2009 10:43 am
Post subject: Re: How to assign MAX(ROW_NUMBER) to a variable [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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. >> Stay informed about: How to assign MAX(ROW_NUMBER) to a variable |
|
| Back to top |
|
| |
External
Since: Sep 08, 2010
Posts: 1
|
(Msg. 8) Posted: Wed Sep 08, 2010 1:25 pm
Post subject: Assigning values from CTE [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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 jtertin wrote:
> 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!
>> On Wednesday, December 09, 2009 6:53 PM Erland Sommarskog wrote:
>> jtertin ( ) 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, esquel.DeleteThis@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)
>>>>>> ----------------
>>>>>>> On Sunday, December 13, 2009 2:22 PM --CELKO-- wrote:
>>>>>>> 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.
>>>>>>> Submitted via EggHeadCafe - Software Developer Portal of Choice
>>>>>>> Simple .NET HEX PixelColor Utility
>>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/5617a491-963d-4510-b8f1-18...df52bc1 >> Stay informed about: How to assign MAX(ROW_NUMBER) to a variable |
|
| Back to top |
|
| |
External
Since: Mar 12, 2004
Posts: 8
|
(Msg. 9) Posted: Wed Sep 08, 2010 3:22 pm
Post subject: Re: Assigning values from CTE [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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" wrote in message
> 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 jtertin wrote:
>
>> 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!
>
>
>>> On Wednesday, December 09, 2009 6:53 PM Erland Sommarskog wrote:
>
>>> jtertin ( ) 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, esquel.TakeThisOut@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)
>>>>>>> ----------------
>
>
>>>>>>>> On Sunday, December 13, 2009 2:22 PM --CELKO-- wrote:
>
>>>>>>>> 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.
>
>
>>>>>>>> Submitted via EggHeadCafe - Software Developer Portal of Choice
>>>>>>>> Simple .NET HEX PixelColor Utility
>>>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/5617a491-963d-4510-b8f1-18...df52bc1 >> Stay informed about: How to assign MAX(ROW_NUMBER) to a variable |
|
| Back to top |
|
| |
| Related Topics: | SELECT row_number() over (order by @order_by) passes synta.. - /* Using variables for dynamic sorting SELECT row_number() over (order by @order_by) executes without an error but doesn't do anything. Is this a bug or am I doing something wrong? Using SQL Server 2005 SP2 */ if object_id('tempdb.dbo.#tRowNumber')...
set a return value to a variable - Hi, I have a dynamic sql that return an integer and i want to assign this integer to a variable. How can I do this? I don't want to create a function since I will have multiple dynamic queries and variables that needed to be code in one sp. any..
table variable ? - Ok I have this, which pulls in 9 rows Declare @revoked Table (TopsId int, TopsStatus int) Insert Into @revoked select MemberID, [Status] from MemberMaster Where [Status] = 8 and RecordLastUpdated > '01/01/08' Then I want to use those table variable...
Table variable problem - Hi, I'm using mix of table variable and temp tables in the procedures. Sometimes table variable works faster sometimes temp table does better. There's some cases I looked at the execution plan saying it's using Table SCAN and the statistics is missing....
Declare variable within Catch block ? - Can I declare a variable within a CATCH block and if so what is its scope? Will it only be created if the CATCH block is activated? Thanks. |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
