Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Getting variable name from cursor as part of query results Expand / Collapse
Author
Message
Posted Thursday, November 15, 2012 6:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 11:49 AM
Points: 18, Visits: 80
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
Posted Thursday, November 15, 2012 6:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 12,744, Visits: 31,071
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
Posted Thursday, November 15, 2012 6:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 11:49 AM
Points: 18, Visits: 80
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
Posted Thursday, November 15, 2012 6:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 11:49 AM
Points: 18, Visits: 80
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
Posted Thursday, November 15, 2012 7:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 11:49 AM
Points: 18, Visits: 80
Figured it out. Thanks for your help.
Post #1385137
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 @ 11:47 PM
Points: 35,959, Visits: 30,252
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1385164
Posted Thursday, November 15, 2012 8:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 11:49 AM
Points: 18, Visits: 80
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
Posted Thursday, November 15, 2012 8:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 11,990, Visits: 11,007
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1385177
Posted Thursday, November 15, 2012 8:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 11:49 AM
Points: 18, Visits: 80
Actually, i cannot provide the code. Let me see what I can do with a do while loop.

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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 11,990, Visits: 11,007
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1385215
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse