I am using a Dynamic SQL to create a stored procedure. The stored procedure is as follows:
CREATE PROCEDURE spRetrieveBalanceSheet (@Pfcode varchar(5), @period varchar(10)) AS BEGIN DECLARE @tablename varchar(12), @sql varchar(2000) SET @tablename='SALFLDG'+Upper(@Pfcode) SET @sql='SELECT S.ACCNT_CODE As Code, SUM(S.AMOUNT) AS Amount, A.ACCNT_NAME AS Name FROM '+ @tablename + ' S JOIN SSRFACC A ON A.ACCNT_CODE = S.ACCNT_CODE WHERE S.ACCNT_CODE BETWEEN 10000 AND 37900 AND S.PERIOD BETWEEN 1990001 AND '+ @period + ' AND A.SUN_DB = ''' + @Pfcode + ''' GROUP BY S.ACCNT_CODE, ACCNT_NAME ORDER BY S.ACCNT_CODE' EXEC(@sql) END
Then for the purposes of Report.rdkc (so that I can drag the three columns to my report.rdlc from the data sources window) I added a Dataset to the project. I selected stored procedure and then selected my stored procedure. The problem is since this is dynamic sql the dataset is not recognising the columns of the stored procedure and therefore I am not able to see the columns in the datasources window for my dataset. Only If i see the columns, I can drag and drop them into the report.rdlc. Can this be done any other way programmatically. The dataset is not at all recognising my stored procedure. If i do not use dynamic sql, then everything is working fine and I am able to drag and drop. I searched the whole net, but couldnt find a solution, my job depends on this. Please help me out.