Report Viewer with dynamic SQL

[binoy.mathew]

Hi,

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.

[vinayphadke]

Hi,

As far as I see if you are using this dynamic statement to 'generate' just SP then its of no use. Since this sql statement will only create a SP. But in order to get the data in you dataset( .xsd), you need to execute the SP; by which you will be able to get the data in your dataset(.xsd) and then drag and drop your columns from dataset to your .rdlc reports. But any way your purpose can be served without using SP too. Basic point here is you need to get data in dataset and hence need to execute the SP.

So if you build a dynamic SQL first to generate the SP and then execute it with the said parameters then you can populate the data.

Hi,

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.