Search code examples
sql-serverstored-proceduresreporting-servicesrdlcreport-viewer2010

Visual studio 2013 dataset not showing up column name for dynamic query of stored procedure

I'm using a dynamic query in stored procedure as a tableadapter while creating a dataset in visual studio 2013. The problem is Data Column is not showing up Parameters and field for SELECT operation. Please find the screenshot: no parameters

I'm not sure if this dynamic statement will work in this case.. Anybody know any possible way to use this stored procedure as datasource in my report. query in stored procedure.

DECLARE @DynamicSQL nvarchar(4000)
SET @DynamicSQL = N'SELECT * FROM ' +@TableName + 
' WHERE ClassName='+@OperationName+' AND (RunTime BETWEEN '+@Date1+' AND '+@Date2+')
AND TestCaseId = ISNULL('+@TestCaseId+', TestCaseId)
 AND TestRunResult = ISNULL('+@TestRunResult+',TestRunResult) 

EXECUTE sp_executesql @DynamicSQL
Solution

  • If the schema doesn't change then you can try this:

    Start your stored procedure with something like this pseudo-code so SSRS knows what columns to expect:

    IF 1=0
  SELECT 
    CAST('SomeDummyData' AS Field1DataType) AS Field1Name
  , CAST('SomeDummyData' AS Field2DataType) AS Field2Name
  , ETC...
ELSE
  ...the rest of your stored procedure

    If that's not enough, open your DataSet and hard-code the fields in the Fields tab.