I want to write some ADO code automatically from the definition of my (many) stored procedures.
This depends on being able to query the metadata tables so that I can generate the column definitions returned from the stored procedure.
So far I have managed the following:
select col_name(dep.depid, dep.depnumber) as ColName,
col_length(oj.[name], col_name(dep.depid, dep.depnumber)) as ColLength,
max(dep.depnumber) as MaxDepNo
from sysobjects oj, sysobjects depoj, sysdepends dep
where depoj.name = 'usp_rs_read_ll'
and dep.depid = oj.id
and dep.id = depoj.id
group by col_name(dep.depid, dep.depnumber), col_length(oj.[name],
order by max(dep.depnumber)
This gets me 20 columns, with their names and lengths, but there should only be 19. The SP is as follows:
select LL.CustomerID, isnull(LL.InvoiceID,0) as InvoiceID, LL.SpecialDelivery, LL.PrintedFlag,
LL.LaundryListDate, LL.DeliveryPattern, LL.Route, isnull(LL.SackCount,0) as SackCount,
isnull(LL.HottCount,0) as HottCount, LL.HeavySoilCharge, LL.MinFixIndicator, LL.InvoiceType,
C.[Name], C.SaturdayRoute, X.ExtraSacks, X.DeliveryLocation, X.ShowTLSizes, X.PackingStation,
isnull(LL.ExtraSacks,0) as LLExtraSacks
from LaundryList LL
inner join Customer C on C.CustomerID = LL.CustomerID
inner join CustomerExtra X on X.CustomerID = LL.CustomerID
where LL.LaundryListID = @llist
The extra field returned from the metadata is LaundryListID which is from the WHERE clause.
I can't find anything that filters this out.
Also, I can't get it in the same sequence as the SP. There doesn't seem to be anything in the metadata tables to sequence it by.
All suggestions gratefully accepted.