none
Returning Table from Stored Procedure with Dynamic SQL RRS feed

  • Question

  •  

    Hi

     

    I have to created a SQL at runtime in a Stored Procedure and called it in LINQ but the return type of the Method in LINQ is int. I want to get the table data from the Method.

     

    How can I get the Table data?

     

    Thanks

    Anandraj.A.

    Wednesday, June 25, 2008 11:03 AM

Answers

  • The designer and SQLMetal tools cannot deduce the correct metadata from your stored procedure that constructs & executes dynamic SQL itself.  In fact, no information is deduced at all which cannot be distinguished from from stored procedures that do nothing but return 'int'.

     

    You can still use your stored procedure, except you'll have to write either the DBML or in-code method signature for the stored proc yourself.  So don't add that stored procedure to the list in the designer.  However, in your own partial class you can write the signature for it (and supply the mapping attributes.)  You may have to create data types to represent the returned value. 

     

     

     

     

     

    Thursday, June 26, 2008 8:10 PM
    Moderator

All replies

  • Can you please post some details of what the stored procedure does (or maybe post an example similar to what you're trying to do)? That will help me better understand the problem.

     

    Thanks,

     

    --Samir

    Wednesday, June 25, 2008 4:56 PM
  • Hi

     

    I have SQL like below in a file. Actually these SQLs are generated from C# and stored in a file coz these where clauses and fields are dynamic and it has to be constructed at runtime. Once these SQL statements are generated C# will store this in a Physical file and from Stored Procedure I have to execute and return the result to LINQ.

    What is the best way to acheive?

     

    Thanks

    Anandraj.A.

     

    DECLARE @tmp TABLE (

    Col1 Varchar(100) Null,

    Col2 int not null,

    ...

    ...

     

    )

     

    INSERT INTO @tmp

    SELECT * FROM OPENROWSET

    (

    'MSDASQL',

    'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\\Temp\\;',

    'select * from test#csv'

    )

     

    Update @tmp Set Col1 ='as',Col2=12

    Where Col3='type' and col4='1'

     

     

    Thursday, June 26, 2008 5:16 AM
  • The designer and SQLMetal tools cannot deduce the correct metadata from your stored procedure that constructs & executes dynamic SQL itself.  In fact, no information is deduced at all which cannot be distinguished from from stored procedures that do nothing but return 'int'.

     

    You can still use your stored procedure, except you'll have to write either the DBML or in-code method signature for the stored proc yourself.  So don't add that stored procedure to the list in the designer.  However, in your own partial class you can write the signature for it (and supply the mapping attributes.)  You may have to create data types to represent the returned value. 

     

     

     

     

     

    Thursday, June 26, 2008 8:10 PM
    Moderator