Reader Mail -
Attilla from Norway (I'm quoting directly here so excuse the spelling) asks the following in reference to an article I posted about building a SQL Reporting Services report around a stored procedure:
**
I am trying to conect query varuabels into layout problem is follows
My stored procedure ask tabelname and it shows the data of a tabel given as parameter lets say @tabel
data set works fine. problem is to show on preview there is no field generation by running the stored procedure.my stored procedure is as follows
CREATE PROCEDURE ShowTable @tabel varchar(100)
AS
DECLARE @sSQL NVARCHAR(255)
SET @sSQL = 'SELECT * FROM ' + @tabelEXEC sp_executesql @sSQL
GOit shows tabel contens when you give the tabel name as input parameter
But i cant make a layout for to show the result on Report services.
So query connectivity to layout doesnt work for me. (You have mentioned on your article about using storedprocedure on reporting services)
I hope you can help me to solve this problem and i am sure many out there need this solution.
This approach to using stored procs, while tempting, is not recommended for a couple of reasons. First of all, the query optimizer can't figure out the best execution plan - and it's also a headache for Reporting Services engine to try to figure out the columns. But I feel the biggest reason not to develop stored procs like this is because it leaves you vulnerable to SQL injection attacks.
In other words, if you run this statement, it works as you expect it to:
EXEC ShowTable 'Products'
However, imagine if somehow someone got this to run:
EXEC ShowTable 'Products Drop Table Products'
What do you think would happen? It will select the records and then drop the table! And dropping a table is just a start to the problems they could cause in your database.
In general, building a stored procedure so it returns the same columns each time is a better idea, even if you have to build one or more for each table. This type that builds a SQL statement and executes it is just not a good idea.