Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server Newbies » Getting variable name from cursor as part of...
Add to briefcase
17 posts, Page 1 of 2
12»»

Getting variable name from cursor as part of query results Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
jbm6401
Posted Thursday, November 15, 2012 6:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 10:31 AM
Points: 9, Visits: 55
Before you chastise me on not doing cursors, since I scanned articles before submitting this request, I know cursors are not that fast. But, this is overall a small table and should not be a hassle.

I am doing a cursor to get table names from a table we use to generate data. The @SQL command brings in a variable representing the table name and uses this to query a count of records in that table. The end game is the check on the table to see if it has been populated. That comes later.

What I would like to do is to have in the results pane the name of the variable representing the table name, and the corresponding counts of elements in that table. The intent is to send this to a temporary table to where I can analyze that to make sure the tables have the counts mentioned in the second paragraph.

Right now, I need to just figure out how to get the table name and the counts into the temp table via the cursor.

Thanks for your help.

Joe
Post #1385119
Lowell
Posted Thursday, November 15, 2012 6:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:11 AM
Points: 11,790, Visits: 28,067
go ahead and throw your cursor away.

the data you are looking for is already materialized for you;
the indexes maintain a count of the number of rows for every table.

try this and see if it's not doing exactly what you were trying to do with your cursor:
SELECT so.[name] as [table name]
     , CASE WHEN si.indid between 1 and 254
            THEN si.[name] ELSE NULL END 
            AS [Index Name]
     , si.indid, rows
     FROM sys.sysindexes si
          INNER JOIN sysobjects so
             ON si.id = so.id
     WHERE si.indid < 2 
       AND so.type = 'U' -- Only User Tables
       AND so.[name] != 'dtproperties'
     ORDER BY so.[name]

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1385121
jbm6401
Posted Thursday, November 15, 2012 6:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 10:31 AM
Points: 9, Visits: 55
This may help. What we do is a daily ETL and the tables created. Then we create a table where we send the table name and the record count on what we do. I'll see what I can do with this. Thanks.
Post #1385132
jbm6401
Posted Thursday, November 15, 2012 6:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 10:31 AM
Points: 9, Visits: 55
I guess more information on what I'm trying to get would help.

The table we have actually has the counts, but we have multiple sources. The cursor gets the table names and goes against the actual tables created and counts the records based on the sources. This is a way we try to validate the sources loaded the data into our table.

Does that help?

Thanks.
Post #1385135
jbm6401
Posted Thursday, November 15, 2012 7:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 10:31 AM
Points: 9, Visits: 55
Figured it out. Thanks for your help.
Post #1385137
Jeff Moden
Posted Thursday, November 15, 2012 7:42 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:01 PM
Points: 33,111, Visits: 27,037
jbm6401 (11/15/2012)
Figured it out. Thanks for your help.


What didd you figure out and can you post your solution?

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1385164
jbm6401
Posted Thursday, November 15, 2012 8:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 10:31 AM
Points: 9, Visits: 55
In my select @SQL statement, I needed to put extra single quotes around the variable name. In this case: '''+@tablename+'''

Now my problem is I cannot select from the temporary table I created. I tried creating a permanent table, and the same thing. I cannot view the data because the error messsage says "invalid object name #tmptbl2".

So it appears cursors don't like creating tables.

Now I need to know if a Do While loop will populate a temp or perm table which can be viewed later.

I'm dealing in SQL Server 2008 Release 2 DB.

Thanks, Joe
Post #1385174
Sean Lange
Posted Thursday, November 15, 2012 8:06 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 3:12 PM
Points: 8,957, Visits: 8,523
jbm6401 (11/15/2012)
In my select @SQL statement, I needed to put extra single quotes around the variable name. In this case: '''+@tablename+'''

Now my problem is I cannot select from the temporary table I created. I tried creating a permanent table, and the same thing. I cannot view the data because the error messsage says "invalid object name #tmptbl2".

So it appears cursors don't like creating tables.

Now I need to know if a Do While loop will populate a temp or perm table which can be viewed later.

I'm dealing in SQL Server 2008 Release 2 DB.

Thanks, Joe


If you could post the actual code you are running, ddl for the table(s) involved, sample data and desired output I am 100% certain you do not need anything as complicated, or slow, as a cursor for this.

Please see the first link in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1385177
jbm6401
Posted Thursday, November 15, 2012 8:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 10:31 AM
Points: 9, Visits: 55
Actually, i cannot provide the code. Let me see what I can do with a do while loop.

Thanks,
Post #1385205
Sean Lange
Posted Thursday, November 15, 2012 8:49 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 3:12 PM
Points: 8,957, Visits: 8,523
jbm6401 (11/15/2012)
Actually, i cannot provide the code. Let me see what I can do with a do while loop.

Thanks,


A while loop is no better than a cursor for performance. Not sure why you can't post the code. I can understand not posting real data, just modify the data to simulate the problem. Certainly don't post data that might be confidential.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1385215
« Prev Topic | Next Topic »

Add to briefcase
17 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.