ROWCOUNT question

Hi All,

The below is a UDF that returns the ROWCOUNT for a table. I need to know 
that value
 for WHILE loop.

How do I take the Returned value from the UDF and load it intio a variable 
in the SP where it was called from? Or maybe there is another way.

ALTER FUNCTION [dbo].[RecCount]
(
 @TableName CHAR(15)
)
RETURNS TABLE
AS
RETURN
(

SELECT Rows FROM sysindexes
   WHERE id = OBJECT_ID(@TableName) AND indid < 2
)

Thanks,

Eric 

0
Eric
11/29/2009 5:47:13 PM
sqlserver.programming 1873 articles. 0 followers. Follow

9 Replies
1339 Views

Similar Articles

[PageSpeed] 59

Eric S (xxx_noSpam@Hotmail.com) writes:
> The below is a UDF that returns the ROWCOUNT for a table. I need to know 
> that value
>  for WHILE loop.
> 
> How do I take the Returned value from the UDF and load it intio a variable 
> in the SP where it was called from? Or maybe there is another way.

  SELECT @var = Rows FROM RecCount(@tablenme)

Although I think you would do better without the function at all. And
maybe also the WHILE loop.

> ALTER FUNCTION [dbo].[RecCount]
> (
>  @TableName CHAR(15)
> )

Make the data type sysname. 15 characters is not much for a table name.




-- 
Erland Sommarskog, SQL Server MVP, esquel@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

0
Erland
11/29/2009 6:40:34 PM
Hi Erland,

Thanks for the reply.
I am using a  MERGE statement to insert about 10 mil records into a table of 
230 mil of records and need to run a loop based of the ROWCOUNT of the 
target table. So Instead of me entering that value manually into the code 
all the time, I thought just to automate that. So Itried this way in the SP 
and it seems to work fine as well.

DECLARE @RowCountInTarget Int = 0

SET @RowCountInTarget = (SELECT Rows FROM sysindexes
  WHERE id = OBJECT_ID('Emails') AND indid < 2)

Regards,

Eric

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9CD2C829AA7B5Yazorman@127.0.0.1...
> Eric S (xxx_noSpam@Hotmail.com) writes:
>> The below is a UDF that returns the ROWCOUNT for a table. I need to know
>> that value
>>  for WHILE loop.
>>
>> How do I take the Returned value from the UDF and load it intio a 
>> variable
>> in the SP where it was called from? Or maybe there is another way.
>
>  SELECT @var = Rows FROM RecCount(@tablenme)
>
> Although I think you would do better without the function at all. And
> maybe also the WHILE loop.
>
>> ALTER FUNCTION [dbo].[RecCount]
>> (
>>  @TableName CHAR(15)
>> )
>
> Make the data type sysname. 15 characters is not much for a table name.
>
>
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@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
> 

0
Eric
11/29/2009 7:54:37 PM
Eric S (xxx_noSpam@Hotmail.com) writes:
> I am using a  MERGE statement to insert about 10 mil records into a
> table of 230 mil of records and need to run a loop based of the ROWCOUNT
> of the target table. So Instead of me entering that value manually into
> the code all the time, I thought just to automate that. So Itried this
> way in the SP and it seems to work fine as well. 
> 
> DECLARE @RowCountInTarget Int = 0
> 
> SET @RowCountInTarget = (SELECT Rows FROM sysindexes
>   WHERE id = OBJECT_ID('Emails') AND indid < 2)
 
Since you are on SQL 2008, you should read the rowcount from 
sys.partitions instead. But admittedly sysindexes is convenient.

But beware that if this code is read by a plain user he may not
have VIEW DEFINITION on the table in question, and your SELECT
will return NULL.

I'm not sure why you need the rowcount to run the MERGE in batches (which
I assume you do.)


-- 
Erland Sommarskog, SQL Server MVP, esquel@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

0
Erland
11/29/2009 9:46:25 PM
Erland's point about sysindexes is correct.  It's not guaranteed to be 
accurate, sys.partitions is much more accurate.

-- 
Thanks

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

"Eric S" <xxx_noSpam@Hotmail.com> wrote in message 
news:O5czM3ScKHA.5608@TK2MSFTNGP05.phx.gbl...
> Hi Erland,
>
> Thanks for the reply.
> I am using a  MERGE statement to insert about 10 mil records into a table 
> of 230 mil of records and need to run a loop based of the ROWCOUNT of the 
> target table. So Instead of me entering that value manually into the code 
> all the time, I thought just to automate that. So Itried this way in the 
> SP and it seems to work fine as well.
>
> DECLARE @RowCountInTarget Int = 0
>
> SET @RowCountInTarget = (SELECT Rows FROM sysindexes
>  WHERE id = OBJECT_ID('Emails') AND indid < 2)
>
> Regards,
>
> Eric
>
> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
> news:Xns9CD2C829AA7B5Yazorman@127.0.0.1...
>> Eric S (xxx_noSpam@Hotmail.com) writes:
>>> The below is a UDF that returns the ROWCOUNT for a table. I need to know
>>> that value
>>>  for WHILE loop.
>>>
>>> How do I take the Returned value from the UDF and load it intio a 
>>> variable
>>> in the SP where it was called from? Or maybe there is another way.
>>
>>  SELECT @var = Rows FROM RecCount(@tablenme)
>>
>> Although I think you would do better without the function at all. And
>> maybe also the WHILE loop.
>>
>>> ALTER FUNCTION [dbo].[RecCount]
>>> (
>>>  @TableName CHAR(15)
>>> )
>>
>> Make the data type sysname. 15 characters is not much for a table name.
>>
>>
>>
>>
>> -- 
>> Erland Sommarskog, SQL Server MVP, esquel@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
>>
> 

0
Michael
11/30/2009 5:31:03 AM
Michael Coles (admin@geocodenet.com) writes:
> Erland's point about sysindexes is correct.  It's not guaranteed to be 
> accurate, sys.partitions is much more accurate.
 
Books Online says that this is an estimate. The only accurate is of course
SELECT COUNT(*), but sys.partitions.rows may be good enough for an estimate,
as long as permissions are not an issue. It's certainly faster.


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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
0
Erland
11/30/2009 8:38:35 AM
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9CD362185F408Yazorman@127.0.0.1...
> Michael Coles (admin@geocodenet.com) writes:
>> Erland's point about sysindexes is correct.  It's not guaranteed to be
>> accurate, sys.partitions is much more accurate.
>
> Books Online says that this is an estimate. The only accurate is of course
> SELECT COUNT(*), but sys.partitions.rows may be good enough for an 
> estimate,
> as long as permissions are not an issue. It's certainly faster.

Hi Erland, I have it on pretty good authority that sys.partitions is 
accurate except for very rare circumstances.  In fact, the same person said 
that if you ever use sys.partitions and it returns an incorrect result you 
should file a connect issue on it to bring it to the their attention. 


0
Michael
11/30/2009 3:11:37 PM
Ok Guys, Thanks for the replies.

"Michael Coles" <michaelcoAToptonlineDOTnet> wrote in message 
news:OkB7q9ccKHA.2160@TK2MSFTNGP02.phx.gbl...
> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
> news:Xns9CD362185F408Yazorman@127.0.0.1...
>> Michael Coles (admin@geocodenet.com) writes:
>>> Erland's point about sysindexes is correct.  It's not guaranteed to be
>>> accurate, sys.partitions is much more accurate.
>>
>> Books Online says that this is an estimate. The only accurate is of 
>> course
>> SELECT COUNT(*), but sys.partitions.rows may be good enough for an 
>> estimate,
>> as long as permissions are not an issue. It's certainly faster.
>
> Hi Erland, I have it on pretty good authority that sys.partitions is 
> accurate except for very rare circumstances.  In fact, the same person 
> said that if you ever use sys.partitions and it returns an incorrect 
> result you should file a connect issue on it to bring it to the their 
> attention.
> 

0
Eric
11/30/2009 8:16:00 PM
Michael Coles (michaelcoAToptonlineDOTnet) writes:
> Hi Erland, I have it on pretty good authority that sys.partitions is 
> accurate except for very rare circumstances.  In fact, the same person
> said that if you ever use sys.partitions and it returns an incorrect
> result you should file a connect issue on it to bring it to the their
> attention. 
 
Nevertheless Books Online says: "Approximate number of rows in this 
partition."



-- 
Erland Sommarskog, SQL Server MVP, esquel@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

0
Erland
11/30/2009 10:43:16 PM
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9CD3F15276205Yazorman@127.0.0.1...
> Michael Coles (michaelcoAToptonlineDOTnet) writes:
>> Hi Erland, I have it on pretty good authority that sys.partitions is
>> accurate except for very rare circumstances.  In fact, the same person
>> said that if you ever use sys.partitions and it returns an incorrect
>> result you should file a connect issue on it to bring it to the their
>> attention.
>
> Nevertheless Books Online says: "Approximate number of rows in this
> partition."


Yup, it does.

-- 
Thanks

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

0
Michael
11/30/2009 11:15:15 PM
Reply:

Similar Artilces:

Time Conversion question
One of the pieces of software we use gives us time in an awkward format. 16633 means 166 minutes 33 seconds. I need a formula to convert this figure to a decimal respresentation of time (166.55 for the example listed previously). Any help will be much appreciated. =INT(A1/100)+MOD(A1,100)*5/300 HTH. Best wishes Harald "Sean Skallerud" <SeanSkallerud@discussions.microsoft.com> skrev i melding news:050BD3C6-E67A-4F94-B08B-695A038B33A6@microsoft.com... > One of the pieces of software we use gives us time in an awkward format. > 16633 means 166 minutes 33 seconds. I...

RTV Questions
I noticed that I can enter many items on an RTV transaction. When I go to the RTB Shipping window and check mark the M column on one of the items, it asks if I want all items for that RTV transaction marked. Do you need to enter the cost for each item? If I mark all items on the RTV transaction and hit the Post button, many payables credit memos are created. Will it create many payables credit memos or is there a setting to create only one credit memo? >I noticed that I can enter many items on an RTV transaction. When I go to the RTB Shipping window and check mark the M column on...

XSL If Question
Hi, I am using XSL if to look and see if a node exists and if so display it with a header (see below). Some of the xml I transforming has nodes like this <Test /> and it shows up in my html report (header no value). How do I test for this? <xsl:if test="Test"> <xsl:if test="Test"> <b><xsl:text>Header: </xsl:text></b> <xsl:value-of select="Test"/> <br></br> </xsl:if> Thanks test for: Test[normalize-space(node())] Cheers, Dimitre Novatchev "Fred" <fred@fred.com> wrote...

Question about installing the CRM-Exchange E-mail Router on Exchange server with Mail Essentials anti-spammer installed
Getting ready to install the CRM-Exchange e-mail router on my Exchange 2000 Server. We also happen to have the Mail Essentials Anti-Spam software installed on this exchange server. Does anyone know of any negative interactions or anything I need to be aware of before installing the E-mail router? Thanks for the response! -Dave Vandenberghe dave@global-image.com We too are using third-party anti-spam software on our Exchange 2K server and it hasn't had any conflict with the Email Router for CRM. -Gary "Dave Vandenberghe" <dave@global-image.com> wrote in message ne...

General Novice Questions (Protection, Format)
I have created a spreadsheet and have restricted a row and footing row (totals) from being changed by using the "format cells / protection" feature. I have left all the data entry rows unprotected so that the entry people can make changes. These entry rows have columns with specfics formats like Date, Currency, etc and I make it all look nice with the grid feature. Finally, I set "Tools /Protection/ Protect sheet" to active the protected cells. The problem is how can the user Insert new rows? To get around this, I created enough rows so that the user has the space they n...

Outlook question #8
I'm using Microsoft Outlook 2007 and am having trouble setting my email up correctly. I am unable to see animations and hear music within an email. Outlook Express had this capability, but doesn't Outlook, it seems to be a superior product from OE. MS <MS@discussions.microsoft.com> wrote: > I'm using Microsoft Outlook 2007 and am having trouble setting my > email up correctly. I am unable to see animations and hear music > within an email. Outlook Express had this capability, but doesn't > Outlook, it seems to be a superior product from OE. For securi...

Question???
I have a spreadsheet with calculations I also have a website and I wa wondering is there a way to cut out a section of an excel spreadshee to just show that on my webpage or will I have to have the whole exce page open to get my calculations to work. Basically I want a little bo on my webpage with the small spreadsheet but I don't want to have t open the whole page. Not sure If I explained that well enough...lo -- CadensDa ----------------------------------------------------------------------- CadensDad's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1746 Vi...

Simple Report Formatting Question
Good morning everyone. I have what I think is a fairly simple question, with hopefully a simple answer. Question is regarding the "Can Grow" property. The fields in the DETAILS section of my report contain borders. I have the CAN GROW property set to Yes for all fields. My question is - if one of the fields needs to grow to accomodate text data, how can instruct the remaining fileds to also grow to the same size. The right most field in my report contains user comments (it is a memo field), and quite often it needs to grow, but the remaining fields in the row do not grow. The r...

SUMIF question #3
Hi, I have a workbook with column A formatted as dates, and column B containing numbers. I would like to know how to get excel to tell me how much the sum of the numbers would be for a given time frame. So, for instance, if I wanted to know the sum of the numbers in column B that have year 2006 in column A, etc. There must be a way to do this, but I'm stumped. Also, can the criteria in the date be narrowed to give me a sum of just, say, a given month within a given year? Thanks in advance, Jim Try this: With Dates in A1:A30 Amounts in B1:B30 Here are 2 variations of the same formu...

Question
I have a typical office network configured under windows 2003 server. If the shared folders stored on the server suddenly start getting disconnected from each terminal, that means that my DNS on each terminal has been wrongly configured, or there is another thing to consider? Thanks JPG Can you post unedited ipconfig /all from server and client? -- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "Juan Pablo Gallardo" wrote: >I h...

Public Folders migration question
Hi there, I've just finished migrating all our mailboxes & public folders to a new Exchange 2k3 server (from Exch 2k). I've followed the steps in 307917 to remove the first exchange server from the site. I'd set the new server as a replication partner for all public folders (incl. system folders: Offline Address Book, OAB Version 2, Schedule+, & Internet Newsgroups), allowed for full replication to occur. I rehomed RUS & designated the new server as routing group master. Yesterday, I turned off all services on the old mail server. Today, the queue (queue name: OLD...

Excel Spreadsheets Reformat Question
I have a disk full of Excel files; I suspect they were created by exporting from an accounting program to Excel to share the financial information with others. Each of these files needs to be PDFd and Bates stamped for production in a legal matter. The problem is this: every single one of these Excel files is not formatted to display all of the information - in order to get a readable page, the columns need to be reformatted to display all of the information contained in each of them, then saved. There are literally hundreds of these files; would there be an easier way of gett...

Printing question
This is a very general question so I might miss something here but this is more of help direction than anything. I need to develop a web app (may be create a silverlight client) which reads bunch of xml and associate them appropriate XSLT and allow users to print them. What is the best way to go about doing it? Thanks, CSharper wrote: > This is a very general question so I might miss something here but > this is more of help direction than anything. > > I need to develop a web app (may be create a silverlight client) which > reads bunch of xml and associate t...

My question gets deleted
Why is my question gets deleted ? I posted twice and it got deleted twice.... I'm getting mad... who has the authority to delete posts in this group ? Julien, Your posts are there. Are you sure your newsgroup reader is not set to hide postes that have already been viewed? Marc "Julien Bonnier" <julien@m0851.com> wrote in message news:OdGmzENGJHA.1272@TK2MSFTNGP02.phx.gbl... > Why is my question gets deleted ? > > > I posted twice and it got deleted twice.... I'm getting mad... who has the > authority to delete posts in this group ? > ...

doc/view question #3
I have a doc/view exe that I created as Explorer-style, which means that each doc has a splitter window like windows Explorer. When the user moves the splitter position, I want the doc to be able to save this info, and restore it when the doc is reopened. How can I accomplish this, since the splitter lives in the ChildFrm rather than a view? Also, how can I create MDI child windows maximized? As the help suggests, in CChildFrm I overrode the PreCreateWindow method to add the WS_MAXIMIZE style, but this didn't work. Lastly, how can I maximize the application itself? Thanks for t...

general question re jpg files
I have no problem opening jpg files when they reside in an e-mail message or even in a folder in 'Computer'. My problem is this, when I go to open a jpg file on my Desktop, nothing happens until I right click and 'Open with' and then make a choice. What don't I understand here. I'm running Vista Home Premium, SP2 and up to date with the lastest WU. Thanks, Dave Horne "Dave Horne" <davehorne@home.nl> wrote: >I have no problem opening jpg files when they reside in an e-mail message or >even in a folder in 'Computer'. My pr...

Publisher question
Can I change a Publisher file to a .jpg or .tif format? How? sally <sally@discussions.microsoft.com> was very recently heard to utter: > Can I change a Publisher file to a .jpg or .tif format? How? Publisher 2002 and 2003 you can go to File > Save As and select TIFF or JPEG format. Publisher 2000 or earlier you will need a third-party app like FinePrint (www.fineprint.com) or SnagIt (www.techsmith.com) -- Ed Bennett - MVP Microsoft Publisher "Ed Bennett" wrote: > sally <sally@discussions.microsoft.com> was very recently heard to > utter: > &...

Basic questions
Hello, I have never seen or used Windows Server and I have a few basic questions before switching Windows XP Pro into WS 2008 R2. Will I be able to install and use other softwares normally as if I was using Windows XP? I have other servers I would like to use with it and I need to know in advance if they will be 100% compatible: Oracle XE 10g MySQL (latest version) Apache 2.2 FileZilla server Windows Media Player 12 sharing Ultra VNC So, please let me know if the above software would be OK and besides that, I would have to test some programs I develop by myself, so I ne...

spacing question
I have a document set up with double spacing. The spaces between paragraphs, however, are double that. How can I make the spaces between paragraphs the same as between lines of the document? Thanks In the Paragraph dialog, make sure Spacing Before/After is set to 0. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "richard" <rmk@wonderland.net> wrote in message news:eZIzyFj9KHA.3840@TK2MSFTNGP02.phx.gbl... >I have a document set up with double spacing. The spaces between >paragraphs, however, ar...

Question
We have two users in our organization that we wish to limit the size of mail they sent to each other but no one else. Does anyone know how to do this? Thanks in advance, Michael "Michael Henderson" <mhenderson@nospam.amerlink.com> wrote: >We have two users in our organization that we wish to limit the size of mail >they sent to each other but no one else. > >Does anyone know how to do this? I don't think you can. But why look for a technical fix for the problem when a simple warning to them should suffice? Would you chain them to their desks if you found ...

Spilt Screen Question
I'm trying to fix the upper left corner (Section I) of the worksheet that is divided like this: Sec I * Sec II * * * ************************** Sec III * Sec IV * * * * I really only want to split it on the vertical. On the horizontal I want to keep section II and IV together. Right now, IV disappears under II when scrolling. But I want III to disappear under I when scrolling because I'm trying to fix I. I also want IV to disappear under III when scrolling. What do you think? Joe Sounds like you should be using Free...

Xslt question: how to transform children of a node into siblings?
Hi, I need some help figuring out how to do something in xslt. I have an xml document with a list of items. Each item in turn can have a list of options, like so: <item> <stuff>Item 1 Stuff</stuff> <option> <option_stuff>Option 1 Stuff</option_stuff> </option> <option> <option_stuff>Option 2 Stuff</option_stuff> </option> ... </item> .... I need to transform that by turning the option tags into item tags, and making them siblings of their parent item, like so: <item> <stuff>...

Safe Pay Formatting Question
We are attempting to use Safe Pay to transmit a positive pay file for Bank of America and I'm having a couple of issues. Has anyone been able to achieve a Hash Total of the check numbers included in the file (where each check issue number is added together)? I'm also curious if anyone's been able to get a cutoff date into their file without manual manipulation? I guess any words of advice on this product would be helpful, I'm finding it seems rather limited...thanks! I'm just curious if you have figured this out. We are about to begin an initiative to produce produ...

Question About Sorting
I want to make a spreadsheet that shows multiple book authors and the books they have written. I want to set it up so that I can sort the list by last name of the author, but the books they write need to go with their name as it is moved. I have tried using column A for the author and column B for the books, but when column A authors is sorted, their books don't go with their name. I also tried indenting their books under their name with the same results. The data doesn't have to be in two columns, but their books need to go with their name. What can I do to make thi...

A question on vba
I have Excel 2000 installed in my computer, and probably need to do some vba programming. I have no experience at all. My first question is: does the vba come with Excel, or is it a separate software that I have to purchase and install? Thanks. Hi Dale, VBA comes with Excel. Change Security level from High to Medium Tools, Macros, Security, Medium Getting Started with Macros and User Defined Functions http://www.mvps.org/dmcritchie/excel/getstarted.htm Some Examples of Macros in (see my excel.htm page as well) http://www.mvps.org/dmcritchie/excel/proper.htm http://www.mvps...