Re: sql query which has got 3 dynamic parameters

Vic (vikrantp@xxxxxxxxx) writes:
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?

If I go back to your original query, it appears that all these tables
have the columns [01-RecordClass], [02-RecordAction], AutomationType
and [Negative Testcase]. So why are not all these in one table where
'accounting' etc is a key?

Judging from the table names, I would guess the table are real business
tables, but those funny names seems odd in that context. So I guess
that what you have in these tables are test data about various functions
in some system. But I understand then why there several tables and not
one.

If you cannot change that design or add any views to alleviate the
situation, dynamic SQL is your way out. See this article on my
web site for details: http://www.sommarskog.se/dynamic_sql.html.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Views
    ... occurs with even simple queries. ... If you can't reproduce it with a limited set of test data but can do so ... My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis ...
    (microsoft.public.sqlserver.clients)
  • Re: Maximum number of fields
    ... Doing a test with Word 2003 and SQL Server 2000 here I could access a 1000-column table via the old SQL Server OLE DB provider - the Edit Recipient dialog does become pretty useless, but the full field list is displayed in the merge field drop and you can insert your own fields manually if that fails. ... limitations on the total amount of data you can have in a record, e.g. AFAICR there's a number of fields per record limit of 1024 in SQL Server 2000), but a maximum data per row of something quite small like 8192 bytes or some such - although that must exclude blob-type fields, and I suspect that all those limits have been relaxed considerably in later versions of SQL Server. ... Personally, I'd consider creating test data with something like double the number of columns you have at present, using real data, and see how Word deals with it. ... Does this exceed the Word single mailmerge limit? ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Export from one DB table into another DB table based on ID number
    ... A few rows of well-chosen test data to illustrate the problem and all ... "text" datatype and use a small description in the test data; ... these in a tabular format, then make sure to use a fixed-width font when ... My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis ...
    (microsoft.public.sqlserver.mseq)
  • Where can I download some free name/address/phone test data?
    ... Need to populate a SQL Server contact database with test data. ...
    (microsoft.public.sqlserver.programming)