I have a query which works fine when hardcoded which is as follows

SELECT SchemaFields.SchemaID, SchemaFields.ObjectClass,
SchemaFields.ObjectType, 'New' AS ActionType, DataQuery.Count

FROM SchemaFields LEFT OUTER JOIN

(SELECT [01-RecordClass], [02-
RecordAction], COUNT(*) AS Count

FROM TestData6061.dbo.accounting

WHERE (AutomationType = 'Loader') AND
([Negative Testcase] = 0)

GROUP BY [01-RecordClass], [02-
RecordAction]) AS DataQuery ON DataQuery.[01-RecordClass] =
SchemaFields.ObjectType AND

DataQuery.[02-RecordAction] = 'New'

WHERE (SchemaFields.SchemaID = 133)

GROUP BY SchemaFields.SchemaID, SchemaFields.ObjectClass,
SchemaFields.ObjectType, DataQuery.Count

In the above query 'TestData6061', 'accounting' (from the 2nd select
statement) and 'SchemaID' are all going to be dynamic. Is there anyway
I can generalize this using functions/stored procedures etc

On Feb 21, 5:01 pm, Vic <vikra... (AT) gmail (DOT) com> wrote:
I am sorry there are not 3 dynamic parameters, but 'accounting' is the
only parameter which is dynamic. accounting is one of the tables under
TestData6061 and there are bunch of others so I want to have a query
which will give me table names for 'TestData6061' in the above example

Did you conceder using sp_executesql?

MarcM

"Vic" <vikrantp (AT) gmail (DOT) com> wrote

Vic (vikrantp (AT) gmail (DOT) com) writes:
Generally, when there are many tables with the same schema which makes
you long for being able to parameterise the table name, the general
opinion is that there is a flaw in the design. A table is intended to
describe a unique identity, and therefore all tables are expected to
have a unique set of columns. And thus there would be little reason to
parameterise table names - and therefore there is no direct feature to
do this.

If you give more details on why you have all these similar tables, we
can give ideas on how to improve the design. Or, if you are stuck with
it, what possible workarounds there may be.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Feb 22, 2:31 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Thanks Erland for the reply first of all. The thing is like I didn't
really design the system. So here is what I came up with so far. I
have a store procedure to which I pass a number which will give me a
particular entry in that database. say if I type exec SP_FETCH_ROWS 1
its going to return accounting or if I pass 4 its going to return
calendar etc

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER procedure [dbo].[SP_FETCH_ROWS](@num int)
AS
BEGIN
SELECT Table_Name from TestData6061.INFORMATION_SCHEMA.Tables a where
@num =(select count(*) from TestData6061.INFORMATION_SCHEMA.Tables b
where a.Table_Name >= b.Table_Name)
END

So now I tried call this in a loop as below

declare @Count int
declare @MaxCount int
set @Count = 0
set @MaxCount = 29
while @Count < @MaxCount
begin
Set @Count = @Count + 1
if @Count = 1 or @Count = 4 or @Count = 7 or @Count = 8 or @Count
= 14 or
@Count = 17 or @Count = 18 or @Count = 22 or @Count = 25 or
@Count = 27 or @Count = 28 or @Count = 29
exec releases.dbo.SP_FETCH_ROWS @Count
end

which isn't working though. The above loop ideally will give me list
of items at those specific positions in
TestData6061.INFORMATION_SCHEMA.Tables

*** The above loop works when I run it through 'New Query' on the sql
server UI but when I open up one of the tables on the db and n then
click on the 'show sql pan' and enter the same thing it only return
'accounting' *** Any idea how this could be fixed?

Vic wrote:

And then you go on at length, without answering his question.

Why does the system contain all these similar tables (e.g. A, B, C),
instead of a single table with an extra column (whose value is e.g.
A, B, C)? Note that you may want to include that extra column in
one or more indexes, for speed.

Do you have the ability to redesign the system? If not, then can you
talk with someone who does? It's worth investigating whether the cost
of such redesign would be repaid by making it easier to work with the
system afterward.

Vic (vikrantp (AT) gmail (DOT) com) writes:
First: don't name your procedures sp_something, as the sp_ prefix is
reserved for system procedures.

Next, I don't understand what this SP_FETCH_ROWS is supposed to
achieved. It returns a certain table name given its position in the
current collation, but I don't see what this could be useful for,
neither in general, nor in relation to the problem in your original
post.

And then someone adds accounting_bck for some reason and your numbers
get out of sync?


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Feb 23, 3:27 am, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

So it seems like I don't really have to look into the Information
shcema for TestData6061 or any other tables. This data can be accessed
from SchemaFields as below

SELECT DISTINCT ObjectClass
FROM SchemaFields
ORDER BY ObjectClass

and this will give me a list something like this ->
accounting
fundstructures
investment
party
etc

Now coming back to the looping, I basically want to have a sql query
which will loop through the above list (accounting, investment, etc)
giving me the single value at a time as it goes through the loop.

Here is what the setup is like just for your information. We have a db
releases under which there is a table called [SchemaFields] which gets
populated with a perl script. We have another set of tables like
'TestData6061' or 'TestData606' etc where we have some data. Now
basically we are going to be running some RDL reports (I am not doing
it, thank god) by relating data in values in SchemaFields and
'TestDataxxx' etc. So for running the reports we basically want to
pass that 'accounting', 'fundstructures' etc and not hard code it.
Does that make sense? I am really sorry if I I didn't make any sense
before but I am completely new to this part of SQL, I just know very
basic sql

Thanks in advance

Vic wrote:

This sounds badly designed. Assuming that these tables look something
like this:

[TestData6061]

CustomerID | FirstName | LastName | TestResult
-----------+-----------+----------+-----------
1 | Winona | Lord | BTN
2 | Billie | Keister | XRC
3 | Daren | Koster | RNC
4 | Valentine | Perkins | FNG
5 | Aleta | Geyer | RWC

[TestData6062]

CustomerID | FirstName | LastName | TestResult
-----------+-----------+----------+-----------
6 | Bronte | Dunlap | SSC
7 | Sinclair | Basmanoff| RRE
8 | Monty | Siegrist | RBN
9 | Suzie | Bailey | NNN
10 | Shantel | Powers | BNM

they should be replaced with a single table like this:

[TestData]

TestID | CustomerID | FirstName | LastName | TestResult
-------+------------+-----------+----------+-----------
0601 | 1 | Winona | Lord | BTN
0601 | 2 | Billie | Keister | XRC
0601 | 3 | Daren | Koster | RNC
0601 | 4 | Valentine | Perkins | FNG
0601 | 5 | Aleta | Geyer | RWC
0602 | 6 | Bronte | Dunlap | SSC
0602 | 7 | Sinclair | Basmanoff| RRE
0602 | 8 | Monty | Siegrist | RBN
0602 | 9 | Suzie | Bailey | NNN
0602 | 10 | Shantel | Powers | BNM

This is the confusing bit. What sort of procedure do you have that is
equally able to operate on 'accounting' and 'fundstructures' and several
other things? Unlike TestData0601 and TestData0602, they don't sound
like they have similar structures.

Are you doing a lot of SELECT * stuff? If so, then that's another thing
that may be good to revise.

On Feb 23, 12:56 pm, Ed Murphy <emurph... (AT) socal (DOT) rr.com> wrote:
So basically we have 2 tables as I mentioned 'SchemaFields' which
looks like this (gets populated by a perl script which parses some
data )

SchemaID Object Object Field Field Field Field
Class Type Name Number DataType Requirement

136 accou Accntg Record 1 genString Mandatory
nting Param Class
eters
136 accou Accntg Record 1 genString Mandatory
nting Param Action 2 ub1
Optional
eters

And then we have our 'TestData6061', TestData606, TestData607 tables
where test data is residing. It'll have test cases with actions as
'New', 'Update' and 'Delete'The idea is to map the above 6 columns
(other than SchemaID) with the data in the test data bases to find out
the coverage for each object and run report which will show a matrix
of
Object Class, ObjectType and New/Update/Delete (from TestData tables)
for these object classes n object types

So while running reports we just want a sql query (either plain select
statement or a stored/procedure, function etc) which is of the form
EXECUTE <StoredProcedure/Function> 'TestData6061', 'accounting', 133
TestData6061 and 133 is obtained from the dropdown list on the report
but the main issue is to iterate through all the object classes which
is 'accounting', 'fundstructures', 'calendar' etc and thats what the
issue is. Doest that make sense?