Re: sql query which has got 3 dynamic parameters

On Feb 26, 2:14 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
Vic (vikra...@xxxxxxxxx) writes:
As mentioned by Ed, yes there are multiple tables which have many
identical columns and they have a few extra columns specific to the
type (say accounting with have its few unique columns same with
fundstructures and other tables in TestData6061). I don't think they
can be merged into a single table, I might be wrong, not sure

What may be the best idea is to construct a view over the common columns:

CREATE VIEW testcases AS
SELECT testcase = 'accounting', [01-RecordClass], [02-RecordAction],
...
FROM accounting
UNION ALL
SELECT 'fundstructures', [01-RecordClass], [02-RecordAction],
...
FROM fundstructures
UNION ALL
...

This presumes that you don't have too many of these tables, as SQL Server
only manages 256 tables in one query.

You would probably have to define dynamically from that file produced by
the Perl script. But from there you could work with the view with static
queries.
--
Erland Sommarskog, SQL Server MVP, esq...@xxxxxxxxxxxxx

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

Thanks everyone for helping meon this. I modified the query I
mentioned earlier to handle 'transaction' seperately (as it was
working for all other object classes) So here is my cursor and the
whole sql query which looks like below. I move on to the next task now
in the same project so might need some sql help later. Thanks

DECLARE @Cmd varchar(5000)
DECLARE @ObjectClass VARCHAR(200)
DECLARE @ObjectClass1 VARCHAR(200)

-- create temp-table to hold the result
CREATE table #result (ObjectClass varchar (100),
ObjectType varchar(200),
ActionType varchar(50),
CountNumber int)

CREATE table #tranresult (ObjectClass varchar (100),
ObjectType varchar(200),
ActionType varchar(50),
CountNumber int)

DECLARE CursorTemplate CURSOR
FAST_FORWARD FOR
SELECT DISTINCT ObjectClass
FROM releases.dbo.SchemaFields
where ObjectClass <> 'transaction'
ORDER BY ObjectClass

OPEN CursorTemplate
FETCH NEXT FROM CursorTemplate INTO @ObjectClass1

WHILE (@@FETCH_STATUS = 0)
BEGIN
Set @Cmd = 'execute releases.dbo.spLoaderCoverageObjectTypeActionType
''TestData6061'''+','+''''+@ObjectClass1+''''+','+'151'
print @Cmd
INSERT #result
EXEC (@Cmd)
FETCH NEXT FROM CursorTemplate INTO @ObjectClass1
END
CLOSE CursorTemplate
DEALLOCATE CursorTemplate

insert #tranresult
EXECUTE releases.dbo.spLoaderCoverageObjectTypeActionType
'TestData6061','[transaction]' , 151

SELECT *
FROM #result

union

select *
from #tranresult
where ObjectClass like 'tran%'
.