CODEWITHSUNDEEP
linq-to-sqlstored-proceduresvb.net-2010
BlueZodiac
BlueZodiac

Reputation: 107

How to use temp table in Stored Procedure with LINQ to SQL

I use a temp table in a Stored Procedure with LINQ to SQL. I add the stored procedure to Linq to SQL dbml file then project occur error message

"Unknown Return Type - The return types for the following stored procedures could not be detected.”

When I delete a temp table in a stored procedure then return value is fine.

How can I use a temp table in a stored procedure with Linq to SQL

I replace the temptable like this

  CREATE TABLE tempTable(
PartsReceivingID INT, 
SoPartID INT,
RecvQty INT,
ReturnQty INT
)

Replace like below

  SELECT @RowCount = count(*)           
  FROM Parts.studentTempTable          
   IF @RowCount > 0         
           BEGIN             
             TRUNCATE TABLE Parts.studentTempTable;
           END

Working version stored procedure

ALTER  PROCEDURE [dbo].[stp_student_Select_New] 
                @pSchID as int, 
                @pCompanyID as int,
                @pAgingDate as int,
                @pTicketNo as VARCHAR(50),
                @pInvoiceNo as VARCHAR(50),
                @pDeliveryNo as VARCHAR(50),
                @pPartNo as VARCHAR(50)
As
SET NOCOUNT ON
BEGIN
          SELECT @RowCount = count(*)
          FROM Parts.studentTempTable

        IF @RowCount > 0
        BEGIN
            TRUNCATE TABLE Parts.studentTempTable;
        END


    ===============================================
    do something with studentTempTable
    ===============================================

        SELECT 
               r.Ticketid AS TicketID,
               r.SoPartNo  AS PartNo ,
               p.Description,
               r.InvoiceNo as InvoiceNo,
               r.InvoiceDate AS InvoiceDate,
               DATEDIFF(DY,r.InvoiceDate,GETDATE())as Aging,
               r.Qty AS CurrentInventory,              
               t.ReturnQty AS ReturnQty
        FROM Parts.studentTempTable AS t,
             Parts.PartsReceiving AS r,
             Parts.PartsInfo as p
        WHERE t.PartsReceivingID = r.PartsReceivingID
          --and i.TicketID = r.TicketID 
          and p.PartID = r.SoPartID  
          and t.ReturnQty >0 
          and DATEDIFF(DY,r.InvoiceDate,GETDATE()) > @pAgingDate
          and r.SchID = @pSchID 
          and r.CompanyID = @pCompanyID
          and r.SoPartNo like  '%%' + @pTicketNo + '%' 
          and r.InvoiceNo like  '%%' + @pInvoiceNo + '%' 
          and r.SoPartNo like  '%%' + @pPartNo + '%' 
          --and i.TicketNo like  '%%' + @pTicketNo + '%' 
          --and r.DeliverNo like  '%%' + @pDeliveryNo + '%' 
Return
END

None Working version stored procedure

ALTER PROCEDURE [dbo].[stp_student_Select] 
                @pVendorID as int, 
                @pCompanyID as int,
                @pAgingDate as int,
                @pTicketNo as VARCHAR(50),
                @pInvoiceNo as VARCHAR(50),
                @pDeliveryNo as VARCHAR(50),
                @pPartNo as VARCHAR(50)

As
SET NOCOUNT ON
BEGIN
    BEGIN TRY


            CREATE TABLE tempTable(
                    PartsReceivingID INT, 
                    SoPartID INT,
                    RecvQty INT,
                    ReturnQty INT
                    )
    ===============================================
    do something with tempTable
    ===============================================
        SELECT 
               isnull(r.Ticketid,0) AS TicketID,
               --i.TicketNo,
               r.SoPartNo  AS PartNo ,
               p.Description,
               r.InvoiceNo as InvoiceNo,
               --r.DeliveryNo,
               r.InvoiceDate AS InvoiceDate,
               DATEDIFF(DY,r.InvoiceDate,GETDATE())as Aging,
               r.Qty AS CurrentInventory,              
               t.ReturnQty AS ReturnQty

        FROM tempTable AS t,
             Parts.PartsReceiving AS r,
             --Ticket.TicketInfo as i,
             Parts.PartsInfo as p

        WHERE t.PartsReceivingID = r.PartsReceivingID
          --and i.TicketID = r.TicketID 
          and p.PartID = r.SoPartID  
          and t.ReturnQty >0 
          and DATEDIFF(DY,r.InvoiceDate,GETDATE()) > @pAgingDate
          and r.VendorID = @pVendorID 
          and r.CompanyID = @pCompanyID
          and r.SoPartNo like  '%%' + @pTicketNo + '%' 
          and r.InvoiceNo like  '%%' + @pInvoiceNo + '%' 
          and r.SoPartNo like  '%%' + @pPartNo + '%' 
          --and i.TicketNo like  '%%' + @pTicketNo + '%' 
          --and r.DeliverNo like  '%%' + @pDeliveryNo + '%' 


        DROP TABLE temptable
    END TRY

    BEGIN CATCH
        SELECT ERROR_MESSAGE() as ErrorMessge,
               ERROR_NUMBER() AS ErrorNumber
    END CATCH
Return
END

Upvotes: 8

Views: 9188

Answers (5)

Ferenc Makó
Ferenc Makó

Reputation: 1

Add below script in beginning of your SP.

IF 1=0 BEGIN
    SET FMTONLY OFF
END

FMTONLY returns only metadata to the dataset. Can be used to test the format of the response without actually running the query.

Above query will give column output without data.

source: https://www.youtube.com/watch?v=zaL7fbUou7E

Upvotes: 0

Jim
Jim

Reputation: 2146

While declaring a table variable will satisfy LINQ to SQL, I have had times where a table variable, due to the lack of statistics, performed horribly.

On those times, I have had to revert to a hack of creating a simple wrapper or shim stored procedure that simply calls the real stored procedure. One requirement of the wrapper stored procedure to make it work is to declare a table variable that matches the output of the real stored procedure and perform an INSERT...EXEC

Declare @Temp table (ColumnA int, ColumnB varchar(256))
Insert Into @Temp(ColumnA, ColumnB)
Exec dbo.OtherStoredProcedure /* pass needed parameters, if any */

Select ColumnA, ColumnB From @Temp

Of course, the definition of the temp table must match exactly the output of the stored procedure. You can't even drop out columns.

LINQ to SQL will not evaluate the "sub" stored procedure at that point and you can call the wrapper stored procedure via LINQ to SQL.

Upvotes: 0

Muhammad Yousaf
Muhammad Yousaf

Reputation: 1

create the temp tables used in sp into orignal db as tables and then use these tables in sp after that drop sp in dbml file, it will return the return type of the sp. after you drop the sp in dbml change the original sp to as it was before and delte the temp table form orignal db

Upvotes: -1

Prahlad
Prahlad

Reputation: 11

If tempTable would really have been #tempTable then to auto generate the class for the result set of the stored proc you have to write this at the beginning of the stored proc definition

IF(1=2)
BEGIN
SELECT
  CAST(NULL AS BIGINT)  AS TicketID --assuming TicketId is of bigint type 
  CAST(NULL AS NVARCHAR(16) AS PartNo --assuming PartNo is of Nvarchar(16)
  .......

  END

Upvotes: 0

jlnorsworthy
jlnorsworthy

Reputation: 3974

If you run the procedure by itself (in SSMS, or Visual Studio) does it return results? Regardless of the answer, I would suggest you use a table variable - what you are currently using is not a temp table - it is just a table. Using a table variable will rule out any issues with actually creating/dropping the table. Googling will find you plenty of information, but this seems to be pretty informative: http://odetocode.com/code/365.aspx

Upvotes: 5

Related Questions