Re: sql query which has got 3 dynamic parameters

On Feb 23, 3:51 pm, Ed Murphy <emurph...@xxxxxxxxxxxx> wrote:
Vic 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

This is unreadable. Suggest you re-post in this style:

insert into SchemaFields (
SchemaId, Object, ObjectClass, FieldType, FieldName, FieldNumber,
DataType, FieldRequirement
) values (
-- first row's data
)

insert into SchemaFields (
SchemaId, Object, ObjectClass, FieldType, FieldName, FieldNumber,
DataType, FieldRequirement
) values (
-- second row's data
)

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

What is the precise layout of these tables?

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?

I think so. Instead of requirements implemented directly as constraints
(which would make it impossible to insert or update data in a
non-compliant fashion), you have requirements based on the contents of
SchemaFields (and want to check what portions of the data are
non-compliant). If you can clean up the data before it hits this
database, then you should switch to constraints; if you intend to store
the data in this database before cleaning, then you can't.

How often does the contents of SchemaFields change?

Would it be simpler to have Perl directly generate SQL code to perform
the desired checks, rather than have it generate data which is then
parsed by SQL?

1st row -
Insert into SchemaFields (SchemaID, ObjectClass,ObjectType,FieldName,
FieldDataType,FieldRequirement) Values
(136,'accounting','AccountingParameters','RecordClass',
1,'genString','Mandatory')

2nd row -
Insert into SchemaFields (SchemaID, ObjectClass,ObjectType,FieldName,
FieldDataType,FieldRequirement) Values
(136,'accounting','FinancialAccount','RecordAction',
2,'genString','Mandatory')

There are many databases like TestData6061, TestData606, TestData607
under which there are tables like 'accounting', 'fundstructures',
'transaction' etc and each of these tables will have 'New', 'Update'
and 'Delete' as the record action. So basically idea is to find out
the coverage for 'accounting' -> 'AccountingParameters' for 'New',
'Update' and 'Delete' by mapping TestData6061/accounting with
accounting and AccountingParamter related rows in SchemaFields

So there were some development and now the status is that I have a
stored procedure written by my boss which is called as
Exec spLoaderCoverageObjectTypeActionType 'TestData6061', 'accounting',
136
The middle argument is dynamic and is taken from the result of
SELECT DISTINCT ObjectClass
FROM SchemaFields
order by ObjectClass




.

Relevant Pages

  • Re: sql query which has got 3 dynamic parameters
    ... looks like this (gets populated by a perl script which parses some ... Class Type Name Number DataType Requirement ... insert into SchemaFields ( ...
    (comp.databases.ms-sqlserver)
  • Re: sql query which has got 3 dynamic parameters
    ... its going to return accounting or if I pass 4 its going to return ... Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ... giving me the single value at a time as it goes through the loop. ... releases under which there is a table called [SchemaFields] which gets ...
    (comp.databases.ms-sqlserver)