I am using mydbr to provide a web based view of existing data in an SQL Server 2005 database (not if our creation) that relies heavily on stored procedures. When using mydbr the following command works and returns some 14 rows of results:
EXEC ABS.dbo.qselGRNByGRNforStockSearch 1234
I need to hide some of the data that is returned depending on various conditions, my initial thought was to capture the output into a temp table and work from that, however when I execute the following code
CREATE TABLE steve_tmp (GRNNumber varchar(128), GRNYear varchar(128), StockFamilyID int, aDescription varchar(128), SupplierID int, SupplierName varchar(128), CustomerID int, CustomerName varchar(128), GRNPrefix varchar(128), GRN int, Description varchar(128), GRNItemNo int, LocalPrice nvarchar, Sizing varchar(128), AddedMaterialDesc varchar(128), GRNItemStatusID int, GRNStatus varchar(128), FullDescription varchar(128), StockFamily varchar(128), GRNItemID int, GRNID int, GRNItemStatus varchar(128), NettWeight decimal, DummyButton varchar(128), ColourCodedButtonStatus varchar(128), Depot varchar(128), CompanyID int, WarehouseID int, DeliveryDate varchar(128), DeliveryDateFilter datetime, LocButton varchar(128), ReservedWeight decimal, ConsignmentReservedWeight decimal, AvailableWeight decimal, AllocatedWeight decimal, ConsignedWeight decimal, ProcessedWeight decimal, SoldWeight decimal, ReturnedWeight decimal, TransferedWeight decimal, LossedWeight decimal, QuarantineWeight decimal, ConsolidatedWeight decimal, StockBreakdownandAmendmentWeight decimal, MissingStock decimal, StockSelectionButton bit, LocalPriceNoFormat float, FormPermitted varchar(128), FormPermittedCategory varchar(128), AvailableForStockSearch bit, AnalysisPercentage decimal, GRNNoAbbreviation varchar(128), WarehouseCode varchar(128), SupplierAccountRef varchar(128), SupplierShortName varchar(128), FutureUseGradeAbbreviation varchar(128), StockCode varchar(128), FormPermittedTypeAbbreviation varchar(128), SupplierAbbreviation varchar(128), SorterGrading varchar(128), LocBinWeightPacking varchar(128), LocBinWeightPackingGrouped varchar(128), FormPermittedAbbreviation varchar(128))
INSERT INTO steve_tmp EXEC ABS.dbo.qselGRNByGRNforStockSearch 1234
SELECT * from steve_tmp
DROP TABLE steve_tmp
This returns the headers from the temp table, but no data.
Any suggestions as to where I have gone wrong would be much appreciated, the only things I can think of are the fields defined as int could in theory be smallints in the stored procedure, and the varchar definitions in the original procedure are not all size 128 - Would this result in the temporary table not having any data?
