none
TableAdapters and Stored Procedures which use Execute RRS feed

  • Question

  • Creating a Data Source for a VB 2010 Windows application. I would like the TableAdapter to access the database using an existing stored procedure. When configure the DataTable and enter my stored procedure, no columns are added to the Data Table. If I right click on the query in the TableAdapter query list and select Preview Data I can pass the two parameters and I see columns in the Results window but these columns are not added to the Data Table.

    I'm assuming the problem is occurring because the source Stored Procedure is building a Varchar SQL statement and then running it using EXECUTE @SQL. It seems that when you select the Stored Procedure for a new Data Source (in VB) it can't pull the column list because the EXECUTE hasn't run and VB doesn't know what columns will be returned.

    Anyone have thoughts on this issue?

    Thank you!


    Wednesday, May 15, 2013 5:43 PM

Answers

  • Solved this by declaring a TABLE variable in the source stored procedure, shoving in the results of EXEC @SQL and then running a SELECT against the TABLE variable.

    Example:

    Doesn't Work

    CREATE PROCEDURE [dbo].[spGenerateReport]
    
    @SearchArguments as VARCHAR(4000)
    
    AS
    
    DECLARE @SQL VARCHAR(8000)
    
    SET @SQL = 'SELECT TotalItems, FY, PONumber FROM vwPurchaseOrderList WHERE ' + @SearchArguments + ' AND (Active = 1)'
    	
    EXECUTE (@SQL)

    Works

    CREATE PROCEDURE [dbo].[spGenerateReport]
    
    @SearchArguments as VARCHAR(4000)
    
    AS
    
    DECLARE @SQL VARCHAR(8000)
    DECLARE @ResultsTable TABLE (  TotalItems int, FY char(4), PONumber char(4)  )
    
    
    SET @SQL = 'SELECT TotalItems, FY, PONumber FROM vwPurchaseOrderList WHERE ' + @SearchArguments + ' AND (Active = 1)'
    	
    INSERT INTO @ResultsTable EXECUTE (@SQL)
    
    SELECT * FROM @ResultsTable

    Good luck!


    • Edited by Jake Fosheezee Wednesday, May 15, 2013 8:46 PM Another missing quote
    • Marked as answer by Jake Fosheezee Wednesday, May 15, 2013 8:47 PM
    Wednesday, May 15, 2013 8:44 PM

All replies

  • Solved this by declaring a TABLE variable in the source stored procedure, shoving in the results of EXEC @SQL and then running a SELECT against the TABLE variable.

    Example:

    Doesn't Work

    CREATE PROCEDURE [dbo].[spGenerateReport]
    
    @SearchArguments as VARCHAR(4000)
    
    AS
    
    DECLARE @SQL VARCHAR(8000)
    
    SET @SQL = 'SELECT TotalItems, FY, PONumber FROM vwPurchaseOrderList WHERE ' + @SearchArguments + ' AND (Active = 1)'
    	
    EXECUTE (@SQL)

    Works

    CREATE PROCEDURE [dbo].[spGenerateReport]
    
    @SearchArguments as VARCHAR(4000)
    
    AS
    
    DECLARE @SQL VARCHAR(8000)
    DECLARE @ResultsTable TABLE (  TotalItems int, FY char(4), PONumber char(4)  )
    
    
    SET @SQL = 'SELECT TotalItems, FY, PONumber FROM vwPurchaseOrderList WHERE ' + @SearchArguments + ' AND (Active = 1)'
    	
    INSERT INTO @ResultsTable EXECUTE (@SQL)
    
    SELECT * FROM @ResultsTable

    Good luck!


    • Edited by Jake Fosheezee Wednesday, May 15, 2013 8:46 PM Another missing quote
    • Marked as answer by Jake Fosheezee Wednesday, May 15, 2013 8:47 PM
    Wednesday, May 15, 2013 8:44 PM
  • Hi Jake,

    Thanks for sharing your solution.

    Have a nice day!

    Best regards,


    Chester Hong
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, May 16, 2013 8:27 AM
    Moderator