none
VS2010: Why can't I import this Stored Proc?? RRS feed

  • Question

  • I have the following stored proc:

    ALTER

     

    PROCEDURE [dbo].[sp_GetBaselines]

    @Request

    VARCHAR(max)

    AS

    BEGIN

     

    -- SET NOCOUNT ON added to prevent extra result sets from interfering with

     

    -- SELECT statements

     

    SET NOCOUNT ON;

     

    -- create the temporary table

     

    CREATE TABLE #tmpTblBaselines

     

     

    (

    MD5

    varchar(32),

    TemplateID

    int,

    FieldID

    int

     

    )

     

    -- insert the records into the temporary table

     

    DECLARE @SQL VARCHAR(max)

     

    SET @SQL = 'INSERT INTO #tmpTblBaselines VALUES ' + @Request

     

    EXEC(@SQL)

     

    -- join the table with the real baseline table

     

    SELECT tblBaseline.MD5, tblBaseline.TemplateID, tblBaseline.FieldID, tblBaseline.Value FROM tblBaseline

     

    INNER JOIN #tmpTblBaselines

     

    ON

    tblBaseline

    .MD5 = #tmpTblBaselines.MD5 AND

    tblBaseline

    .TemplateID = #tmpTblBaselines.TemplateID AND

    tblBaseline

    .FieldID = #tmpTblBaselines.FieldID

    END

     

    When I try to import this into my project via Add Data Source, ADO.NET refuses to create a strongly typed DataSet for the stored proc.

    If I remove the inner join portion, it works fine.

    I suppose a work-around would be to comment out the inner join, import and then uncomment it...

    But that would require doing that on every import. We are still in the early dev stage and the DB is changing a lot. I'd rather not do that :).

     

    Wednesday, July 21, 2010 11:48 PM

Answers

All replies

  • This is because the stored procedure must be compiled but not run.  The result set schema cannot be obtained up front when your stored procedure contains a statement which forces recompilation upon execution of the statement.  CREATE TABLE does this.  Consider rewriting to use a @table variable instead which can be used as a substitute for CREATE TABLE #temp.

    See "Declaring a variable of type table" (http://msdn.microsoft.com/en-us/library/ms188927.aspx).  Note that there are reasons that this could improve performance and reasons why this could decrease performance.  However, if the number of rows in the temporary table is small, it will likely improve performance.

    Some reading material:

    http://www.mssqltips.com/tip.asp?tip=1556

    http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx

    Thursday, July 22, 2010 2:13 AM
  • Hi BinaryCoder,

    Thanks for the response. Did you mean:

    DECLARE @tmpTblBaselines TABLE
    (
    MD5 varchar(32),
    TemplateID int,
    FieldID int
    )

    Is that what you meant?? That did not work either. Well, what I mean is... both the DECLARE and CREATE TABLE method "work" and return the proper data, but when I go to import with the ADO.NET "Add Data Source" wizard, the wizard can't figure out the schema of the result set and just sticks the sp_GetBaselines in the un-typed TableAdapters class.

    As I mentioned in the previous post, the thing thats confusing the wizard is the INNER JOIN... if I just have

    SELECT tblBaseline.MD5, tblBaseline.TemplateID, tblBaseline.FieldID, tblBaseline.Value FROM tblBaseline

    It can figure it out... if I put back the INNER JOIN part, it gets confused, but I don't really want to have to comment / uncomment on every import.

    • Proposed as answer by DK. Da Tuesday, July 27, 2010 2:25 AM
    Thursday, July 22, 2010 3:29 AM
  • A follow up...

    I can't use the DECLARE @tmpTblBaselines TABLE method because @tmpTblBaselines is not visible to the INNER JOIN ON clause.

     

    Thursday, July 22, 2010 4:20 PM
  • Compare with this:

    INNER JOIN @tmpTblBaselines t
    ON tblBaseline.MD5 = t.MD5
    AND tblBaseline.TemplateID = t.TemplateID
    AND tblBaseline.FieldID = t.FieldID
    
    
    Friday, July 23, 2010 3:01 AM