Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
marat
Yak Posting Veteran
85 Posts |
 Posted - 2005-01-20 : 18:20:21
|
| How can I create(for example) a stored procedure or function whichreturns a number of rows for the specified table?Answers like:select * from <table>return @@rowcountare not acceptable.Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-20 : 18:23:57
|
| Why would you want to do that? It is going to require dynamic sql, which we would not recommend. Why do you need the table name to be dynamic? And don't use select * with @@rowcount. You would use:SELECT COUNT(*) FROM TableNameTara |
 |
|
|
marat
Yak Posting Veteran
85 Posts |
Posted - 2005-01-20 : 18:51:09
|
| The reason is the following procedure I created:CREATE PROCEDURE dbo.usp_Generic_Report_on_Table ------------------------------------------------------------------ This stored procedure generates counts on every column in a --- user specified table and e-mails report to the recepients --- Input parameters: --- 1 - table name --- 2 - values report flag, specifies if report on values --- required --- 3 - comparison report flag, specifies if comparison report --- required --- 4 - save report flag, specifies save option --- 5 - recepients of the report --- Output results are stored in <Table_name>_Report_On_Counts --- <Table_name>_Report_On_Values - -- tables if save report flag = 1 --- also shows percentage of totals ----------------------------------------------------------------- @table sysname = '', --reporting table @val_on int = 0, --values report? @diff_on int = 0, --comparison report? @save_on int = 0, --save report? @rcps varchar(250) = 'marat' --recipients It works fine but im my dynamic query I have to put few times(select max(Populated) from ##' + @table + '_counts_rep)Actually I think I can assign above string to @var and then use@var in dynamic query but my question wasn't about it.Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-20 : 18:54:12
|
| If you don't care about the performance of this query, nor do you care that this will affect your system, then:CREATE PROC SomeProc (@TableName sysname)ASSET NOCOUNT ONDECLARE @SQL varchar(7000)SET @SQL = 'SELECT COUNT(*) FROM ' + TableNameEXEC (@SQL)RETURNTara |
|
|
|
marat
Yak Posting Veteran
85 Posts |
Posted - 2005-01-20 : 19:07:57
|
| Thanks Tara.Good point about the performance, it takes between 1sec to 10hours, depends...But that's not the issue. Issue is thant client wants counts on data supplied,also it's a good way to check data after amendments, sort of to have a snapshot,what has been changed since last one, and is everything OK with data. I think it worth it.Thanks a lot. |
|
|
|
|
|
|
|
|
rows count