need help getting results of a dynamic sproc to appear in Linq strongly defined

[Michael J Eber]

I have a vendor application and our contractor is writing some stored procedures to acquire data for my use. The SPROC is in one table and it is creating a temporary table running against the company table. There is then a select for each field in the temp table followed by a delete of the temp table. When I use Linq to SQL and put the stored procedure into my program it insists on only setting the return value as an integer, not the 10 fields from the temporary table. Can anyone suggest how I can get the specific table rows defined in Linq? here is the dynamic stored procedure:

ALTER procedure [dbo].[ioGetInvoices]
@CompanyDBName varchar(50), @CustomerID varchar(100), @CutoffDate as varchar(15)=''

as
declare @SQLString as varchar(8000), @ADate as datetime
--If no date passed use default of 12 months prior to todays date
if @CutoffDate=''
BEGIN
--Get Todays Date
Set @ADate=GetDate()
--Subtract 12 months for cutoff
Set @ADate=DATEADD(month, -12, @ADate)
--Convert time to 0
Set @CutoffDate=convert(varchar(15),@ADate, 101)
END

set nocount on

CREATE TABLE [dbo].#invoices(
[CUSTNMBR] [char](15),
[CUSTNAME] [char](65),
[DOCDATE] [datetime],
[DOCNUMBR] [char](21),
[CHEKNMBR] [char](21),
[RMDTYPAL] [smallint],
[DOCTYPE] [varchar](20),
[TRXDSCRN] [char](31),
[CSPORNBR] [char](21),
[DUEDATE] [datetime],
[Payment Status] [varchar](20),
[CURTRXAM] [numeric](19, 5),
[ORTRXAMT] [numeric](19, 5))

Set @SQLString =
'Insert #invoices select
a.CUSTNMBR, b.CUSTNAME, a.DOCDATE, a.DOCNUMBR,CHEKNMBR,
RMDTYPAL,
DOCTYPE=
Case when RMDTYPAL = 1 then ''Invoice'' --SOPTYPE=3
when RMDTYPAL = 2 then ''unknown''
when RMDTYPAL = 3 then ''Debit''
when RMDTYPAL = 4 then ''Finance Charge''
when RMDTYPAL = 5 then ''Service Repair''
when RMDTYPAL = 6 then ''Warranty''
when RMDTYPAL = 7 then ''Credit''
when RMDTYPAL = 8 then ''Return'' --SOPTYPE=4
when RMDTYPAL = 9 then ''Payment'' end,
TRXDSCRN,a.CSPORNBR,
a.DUEDATE,
[Payment Status]=Case
when RMDTYPAL < 7 and CURTRXAM = 0 then ''Fully Paid''
when RMDTYPAL < 7 and CURTRXAM = ORTRXAMT then ''Unpaid''
when RMDTYPAL < 7 and CURTRXAM > 0 and CURTRXAM <> ORTRXAMT then ''Partially paid''
when RMDTYPAL >= 7 and CURTRXAM = 0 then ''Fully Applied''
when RMDTYPAL >= 7 and CURTRXAM = ORTRXAMT then ''Unapplied''
when RMDTYPAL >= 7 and CURTRXAM >0 and CURTRXAM <> ORTRXAMT then ''Partially Applied''
else ''???'' end,
a.CURTRXAM,a.ORTRXAMT
from '

[Andreas X]

Hello! When i have similar situations i usually create a dummy SPROC that returns only empty data with the correct datatypes for each column. like:

select
CAST(0 as int) as CustomerNumber,
CAST('x' as nvarchar(64)) as CustomerName, .......

and then comment all dynamic data in the SPROC. Then you can drag the SPROC into the dbml to get the correct return type. Once you hava a correct return type. restore your SPROC to the correct state. hope it helps

Andreas Johansson
IT Professional at Office IT Partner i Norrbotten Sweden
What we don't know. We learn.
What you don't know. We teach