Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Create Dynamic SSRS Reports Using a Query as an Input Parameter


By:   |   Updated: 2018-11-06   |   Comments (19)   |   Related: > Reporting Services Dynamic Reports

Problem

When SQL Server Reporting Services (SSRS) is used for retrieving data from a database a report is created. Even for a simple SELECT statement, a new report definition file is created and run against a particular data source. There is a generic problem with this approach. As business requirements increase to obtain different datasets, the number of reports increase as well. Also, there are times when users need to query reporting data and the only interface available for them is SSRS. The end user will have no choice other than creating a new report definition and running it on an SSRS server.

Solution

The solution to this problem is to create a single report which allows a user to pass a query as a report parameter and the output of query is presented as a report in a tabular form. Using this approach, the number of reports can be substantially reduced. Multiple subscriptions can be created against a single report to produce entirely different datasets. And using the SSRS automated email feature, different email alerts can be created from a single report.

Creating a Dynamic SQL Report in SQL Server Reporting Services

This solution appears to be working perfectly when a SELECT query or a combination of select queries such as CTE (common table expression query) is run against a set of tables or views.

The main components of this report are:

  1. A multi-line text parameter which accepts a query as input
  2. A dynamic SQL query in the dataset, which transforms the output of a query to only three columns i.e. RowId, ColumnName and Value
  3. An SSRS matrix which transforms data from ColumnName and Value pair combinations to a tabular structure.

We will work through a step-by-step approach to create a dynamic SQL report.

Creating a Multi-line Text Parameter

Add a new parameter to your empty report as shown below:

Adding SQLTxt parameter to the report
Details of SQLTxt parameter

The report parameter name used in this example is SQLTxt, its prompt is "SQL Text", data type is text, "Allow multiple values" checkbox is checked and "Select parameter visibility" is set to visible. When this parameter is created it will appear in the parameters list as shown below.

SQLTxt appearing in report parameters

Creating a dataset for the dynamic SQL query

Here is the code for the dynamic SQL query which resides in the dataset query text.

SET NOCOUNT ON

BEGIN TRY
   DECLARE @dbCursor CURSOR 
   DECLARE @sqlQuery NVARCHAR(MAX) [email protected]
   DECLARE @tblColumnDetails TABLE (ordinal_position INT, column_name VARCHAR(255))
   DECLARE @column_name VARCHAR(255) , @ordinal_position INT , @column_characteristics_flags INT , @column_size INT , @data_type_sql INT , @column_precision INT , @column_scale INT , @order_position INT , @order_direction VARCHAR(255) , @hidden_column INT , @columnid INT , @objectid INT , @dbid INT , @dbname VARCHAR(255)
   DECLARE @queryDynamicColumnsDec NVARCHAR(MAX)=''
   DECLARE @queryDynamicColumnsLst NVARCHAR(MAX)=''
   DECLARE @queryDynamicColumnsLstDec NVARCHAR(MAX)=''
   DECLARE @queryDynamicTable NVARCHAR(MAX)=''

   SET @sqlQuery = 'DECLARE  query_cursor CURSOR FOR '[email protected]
   EXEC sp_executesql @sqlQuery
   DECLARE @ResultSet CURSOR
   EXEC master.dbo.sp_describe_cursor_columns @ResultSet OUTPUT,@cursor_source=global,@cursor_identity=N'query_cursor'
 
   FETCH NEXT from @ResultSet INTO @column_name, @ordinal_position, @column_characteristics_flags, @column_size, @data_type_sql, @column_precision, @column_scale, @order_position, @order_direction, @hidden_column, @columnid, @objectid, @dbid, @dbname;

   WHILE (@@FETCH_STATUS <> -1)  
   BEGIN  
      INSERT INTO @tblColumnDetails(ordinal_position,column_name) VALUES(@ordinal_position,ISNULL(@column_name,'Col_'+CAST(@ordinal_position AS VARCHAR(6))))
      FETCH NEXT from @ResultSet INTO @column_name, @ordinal_position, @column_characteristics_flags, @column_size, @data_type_sql, @column_precision, @column_scale, @order_position, @order_direction, @hidden_column, @columnid, @objectid, @dbid, @dbname   
   END  
 
   SELECT @queryDynamicColumnsDec += '['+column_name+'] NVARCHAR(MAX),', @queryDynamicColumnsLstDec+='@var_'+CAST(ordinal_position AS VARCHAR(6))+' NVARCHAR(MAX),', @queryDynamicColumnsLst+='@var_'+CAST(ordinal_position AS VARCHAR(6))+','  FROM @tblColumnDetails ORDER BY ordinal_position
   SET @queryDynamicColumnsDec= LEFT(@queryDynamicColumnsDec,LEN(@queryDynamicColumnsDec)-1)
   SET @queryDynamicColumnsLst= LEFT(@queryDynamicColumnsLst,LEN(@queryDynamicColumnsLst)-1)
   SET @queryDynamicColumnsLstDec= LEFT(@queryDynamicColumnsLstDec,LEN(@queryDynamicColumnsLstDec)-1)
   SET @queryDynamicTable = 'DECLARE @dynamicTable TABLE('+REPLACE(@queryDynamicColumnsDec,'@','')+')'
 
   DECLARE @sql1 NVARCHAR(MAX) =
   'DECLARE '[email protected]+CHAR(13)+
   @queryDynamicTable+CHAR(13)+
   'OPEN query_cursor
   
   FETCH NEXT FROM query_cursor INTO '[email protected]+'
   WHILE(@@FETCH_STATUS<>-1)
   BEGIN
      INSERT INTO @dynamicTable('+REPLACE(@queryDynamicColumnsDec,'NVARCHAR(MAX)','')+') VALUES ('[email protected]+')
      FETCH NEXT FROM query_cursor INTO '[email protected]+'
   END

   SELECT RowId,ColumnName,Value FROM (
   SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1))[RowId],'+REPLACE(@queryDynamicColumnsDec,'NVARCHAR(MAX)','')+' FROM @dynamicTable
   )tblBase
   UNPIVOT
   (
   Value FOR ColumnName IN ('+REPLACE(@queryDynamicColumnsDec,'NVARCHAR(MAX)','')+')
   )tblUnPivot
   ORDER BY RowId
   '
   EXEC sp_executesql @sql1
 
   IF CURSOR_STATUS('global','query_cursor')>=-1
   BEGIN
      DEALLOCATE query_cursor
   END
 
END TRY

BEGIN CATCH
   SELECT 1 RowId,'Error'ColumnName,ERROR_MESSAGE()[Value]

   IF CURSOR_STATUS('global','query_cursor')>=-1
   BEGIN
      DEALLOCATE query_cursor
   END
END CATCH			

Note that the report parameter created in the previous step is passed as a parameter to the dynamic SQL query in line number 4. The input query from the report parameter is run as a cursor using sp_executesql stored procedure. The column details of this cursor is obtained by using stored procedure master.dbo.sp_describe_cursor_columns and then the details of the columns are stored in the @tblColumnDetails table variable. Up to this point, the dataset and names of the columns are obtained. When the information for the column names and values are available, sp_executesql is used again with the UNPIVOT operator to transform the query output from multiple columns to three only columns: RowId, ColumnName and Value. This whole dynamic SQL code is wrapped in TRY CATCH blocks for exception handling.

Create a new dataset using dynamic SQL code

One of the steps when adding a new dataset

In the dataset properties set the query type to text. Copy and paste the dynamic SQL query into the dataset query textbox as shown below:

Query tab of dataset properties

Set the timeout value to a reasonable number, for example 600 seconds.

Go to the Fields tab of the dataset properties for manually adding the Fields Name and Fields Source. If these fields appear automatically as shown in the screenshot below, then it’s fine otherwise create as suggested.

Fields tab of dataset properties

Another important aspect in this dataset is to control parameters so that they won’t be created incorrectly. Go to the Parameters tab of dataset properties and check whether you have @cursor_source and @cursor_identity appearing in the list of dataset parameters. They appear from dynamic SQL code and are not needed for dataset parameters. If they appear, then please ensure that @cursor_source and @cursor_identity are deleted. To do that select each of these parameters and hit delete.

Showing two parameters that need to be deleted if they are showing up.

After deleting @cursor_source and @cursor_identity parameters. The dataset properties parameter tab will look like this figure below.

@SqlTxt is a dataset report parameter

Now we need to control the parameter value. In the parameters tab of the dataset properties, set the expression of @SqlTxt parameter by clicking on fx button and adding the following line of code to expression.

=JOIN(Parameters!SQLTxt.Label,CHR(13))
This expression will combine different lines into a single string

This expression will turn a multiple value parameter to a single string with multiple lines in it. That’s what we will pass, a single SQL query with multiple lines in it.

Screenshot after setting up expression

Click OK to create the dataset.

A couple of important points to note:

  • If a dataset is created before deleting the unwanted parameters from the dataset parameters tab, then those parameters will automatically be added to report parameters. If it happens, then remove @cursor_source and @cursor_identity from the dataset parameters and report parameters as well.
  • Another important point to understand is that the queries which are going to be run through this report are not known at this stage, that’s why we need to manually add RowId, ColumnName and Value fields to the dataset properties. We cannot expect that dataset fields will be automatically created.
Dataset with three columns RowId, ColumnName, Value

Creating an SSRS Matrix

From the toolbox, add a new matrix in the report. A new matrix will look like this:

A blank matrix in an SSRS reprot design.

From the dataset created in the previous step, drag and drop each field from the DataSet as marked below:

Dataset with three columns
SSRS Matrix showing where to drop fields from dataset.

After dragging and dropping the dataset fields, the matrix will look like this screenshot below:

SSRS Matrix after drag and drop of fields of

It is possible that in the older versions of Business Intelligence Development Studio that the Value appears with an aggregate function, for example SUM([Value]). To avoid this, right click on the Value textbox and set the expression to =Fields!Value.Value.

Screenshot of expression of Value

At this state the dynamic SQL report is functionally complete. Save the work and preview, it should show output against the different SQL queries.

Correcting Column Sequence

If you preview the report now, you might be able to notice that the sequence of columns from left to right is in alphabetical order A-Z, not in the order specified by the SELECT query. That’s due to the default sort order of a group in a matrix. To change this sequence, select matrix and go to groups. You’ll see two types of groups: 1) Row Groups and 2) Column Groups. In column groups, go to the properties of the ColumnName group and then select the sorting tab. Change the sort by option to RowId. As shown in the figures below.

Steps towards changing group properties
Sorting is changed to RowId instead

Changing the SQL Server Reporting Services Report Appearance

From an appearance point of view, we will make a few minor changes. First of all, we will hide the RowId column because there is no need to display it. To do this, right click, select Textbox Properties and then select visibility tab, and chose "Hide" in the visibility option.

Screenshot of hiding RowId column

Repeat this for the RowId header and RowId details textboxes.

The RowId columns are now hidden and will not appear when the report is run. The next step is to make RowId column small enough so that it will not occupy space. Move the matrix to top left corner and click to adjust the width of RowId to its minimum by moving it to the left as shown below:

Screenshot when size is being changed
After reducing the size of RowId width

Let’s change the default colors and font of the textboxes in the matrix. Right click on the textbox, chose properties and then select the Font tab. In the following example, Calibri font of size 11pt is selected with Bold style.

Changing Textbox properties.

This will change appearance of the foreground text. To change the background color, select Fill tab in the same Text Box Properties window. As shown in the example, the fill color is Cornflower Blue.

Backgroud color being filled up

Select the color and click on OK button to close the dialog box.

The report is now complete and is ready for testing.

Using the Dynamic SQL Server Reporting Services Report

Here are some examples of using the dynamic SQL report in SQL Server Reporting Services 2016 which is connected to the Adventure Works 2016 database.

Example of query 1
Example: Output of query 1
Example of query 2
Example: of Output of Query 2
Next Steps


Last Updated: 2018-11-06


get scripts

next tip button



About the author
MSSQLTips author Laeeq Hamid Laeeq Hamid has more than 10 years of experience working with SQL Server database application design, development and support for Australian government agencies.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, June 25, 2019 - 9:43:33 AM - David Copi Back To Top

Hi, Laeeq:

Here is a version of the SQL query that does not use cursors. It does not include support for stored procedures.

 SET NOCOUNT ON 
BEGIN TRY 
 DECLARE @queryDescription TABLE 
 ( 
 s_hidden                      bit             NULL 
 ,column_ordinal               int             NULL 
 ,name                         sysname         NULL 
 ,is_nullable                  bit             NULL 
 ,system_type_id               int             NULL 
 ,system_type_name             nvarchar(256)   NULL 
 ,max_length                   smallint        NULL 
 ,precision                    tinyint         NULL 
 ,scale                        tinyint         NULL 
 ,collation_name               sysname         NULL 
 ,user_type_id                 int             NULL 
 ,user_type_database           sysname         NULL 
 ,user_type_schema             sysname         NULL 
 ,user_type_name               sysname         NULL 
 ,assembly_qualified_type_name nvarchar(4000)  NULL 
 ,xml_collection_id            int             NULL 
 ,xml_collection_database      sysname         NULL 
 ,xml_collection_schema        sysname         NULL 
 ,xml_collection_name          sysname         NULL 
 ,is_xml_document              bit             NULL 
 ,is_case_sensitive            bit             NULL 
 ,is_fixed_length_clr_type     bit             NULL 
 ,source_server                sysname         NULL 
 ,source_database              sysname         NULL 
 ,source_schema                sysname         NULL 
 ,source_table                 sysname         NULL 
 ,source_column                sysname         NULL 
 ,is_identity_column           bit             NULL 
 ,is_part_of_unique_key        bit             NULL 
 ,is_updateable                bit             NULL 
 ,is_computed_column           bit             NULL 
 ,is_sparse_column_set         bit             NULL 
 ,ordinal_in_order_by_list     smallint        NULL 
 ,order_by_list_length         smallint        NULL 
 ,order_by_is_descending       smallint        NULL 
 ,tds_type_id                  int             NULL 
 ,tds_length                   int             NULL 
 ,tds_collation_id             int             NULL 
 ,tds_collation_sort_id        tinyint         NULL 
 ); 
   
 -- You can get the column info from the query without using a cursor 
INSERT INTO @queryDescription 
EXEC sp_describe_first_result_set @SqlTxt; 
   
 -- Create a comma-separated list of column names to set up the dynamic pivot 
DECLARE @colsNameList NVARCHAR(MAX) = STUFF((SELECT ',' + QUOTENAME([name]) 
                      FROM @queryDescription 
                      ORDER BY [column_ordinal] 
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') 
   
 -- You can also set up and populate the dynamic table without using a cursor 
 DECLARE @sql1 NVARCHAR(MAX) = 
 'DECLARE @dynamicTable TABLE ' + CHAR(10) + CHAR(13) 
 + '(' + REPLACE(STUFF((SELECT ',' + QUOTENAME([name]) + ' NVARCHAR(MAX)' 
    FROM @queryDescription 
    ORDER BY [column_ordinal] 
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') 
       , 1, 1, '') 
             , '@', '') + ') ' 
 + 'INSERT INTO @dynamicTable ' + @SqlTxt 
 + ' SELECT RowID, ColumnName, Value ' 
 + ' FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS 
[RowID], ' + @colsNameList + ' FROM @dynamicTable) tblBase ' 
 + 'UNPIVOT ' 
 + ' (Value FOR ColumnName IN (' + @colsNameList + ')) tblUnPivot 
ORDER BY [RowID]' 
   
EXEC sp_executesql @sql1 
END TRY 
   
BEGIN CATCH 
    SELECT 1 AS RowId, '*** ERROR ***' AS ColumnName, ERROR_MESSAGE() AS [Value] 
END CATCH   

Tuesday, April 30, 2019 - 3:22:34 AM - Laeeq Hamid Back To Top

Hi Adam,

Here is a new version of main SQL query. It can run stored procedures but needs a connection through linked server. I have also made query_cursor a global cursor at the time of its decleration and resolved the issue where a column when all values are null does not appear in the report.

SET NOCOUNT ON
BEGIN TRY
--Add a unique string to identify null columns. Because UNPIOVT operator used below will take out nulls from its output.
DECLARE @strNULLString VARCHAR(20)= '##NULL##'
DECLARE @dbCursor CURSOR
DECLARE @sqlQuery NVARCHAR(MAX) [email protected]
-- Replace 'REMOTE_SERVER_NAME' below with your linked server. You might have to check whether you need a SQL authentication, Windows domain authentication or any other security context
SET @sqlQuery = 'SELECT * FROM OPENQUERY([LOCALHOST1],'+CHAR(13)+''''+REPLACE(@SqlTxt,'''','''''')+''')'

DECLARE @tblColumnDetails TABLE (ordinal_position INT, column_name VARCHAR(255))
DECLARE @column_name VARCHAR(255) , @ordinal_position INT , @column_characteristics_flags INT , @column_size INT , @data_type_sql INT , @column_precision INT , @column_scale INT , @order_position INT , @order_direction VARCHAR(255) , @hidden_column INT , @columnid INT , @objectid INT , @dbid INT , @dbname VARCHAR(255)
DECLARE @queryDynamicColumnsDec NVARCHAR(MAX)=''
DECLARE @queryDynamicColumnsDecAvoidNULL NVARCHAR(MAX)=''
DECLARE @queryDynamicColumnsLst NVARCHAR(MAX)=''
DECLARE @queryDynamicColumnsLstDec NVARCHAR(MAX)=''
DECLARE @queryDynamicTable NVARCHAR(MAX)=''

-- Explicity declaring a global variable for query_cursor to cover different situations as mentioned in the comments section of the article
SET @sqlQuery = 'DECLARE query_cursor CURSOR GLOBAL FOR '[email protected]

EXEC sp_executesql @sqlQuery
DECLARE @ResultSet CURSOR
EXEC master.dbo.sp_describe_cursor_columns @ResultSet OUTPUT,@cursor_source=global,@cursor_identity=N'query_cursor'

FETCH NEXT from @ResultSet INTO @column_name, @ordinal_position, @column_characteristics_flags, @column_size, @data_type_sql, @column_precision, @column_scale, @order_position, @order_direction, @hidden_column, @columnid, @objectid, @dbid, @dbname;
WHILE (@@FETCH_STATUS &lt;&gt; -1) 
BEGIN 
INSERT INTO @tblColumnDetails(ordinal_position,column_name) VALUES(@ordinal_position,ISNULL(@column_name,'Col_'+CAST(@ordinal_position AS VARCHAR(6))))
FETCH NEXT from @ResultSet INTO @column_name, @ordinal_position, @column_characteristics_flags, @column_size, @data_type_sql, @column_precision, @column_scale, @order_position, @order_direction, @hidden_column, @columnid, @objectid, @dbid, @dbname 
END 

SELECT @queryDynamicColumnsDec += '['+column_name+'] NVARCHAR(MAX),', @queryDynamicColumnsLstDec+='@var_'+CAST(ordinal_position AS VARCHAR(6))+' NVARCHAR(MAX),',@queryDynamicColumnsDecAvoidNULL+= 'ISNULL(['+column_name+'],'''[email protected]+''')['+column_name+'],', @queryDynamicColumnsLst+='@var_'+CAST(ordinal_position AS VARCHAR(6))+',' FROM @tblColumnDetails ORDER BY ordinal_position
SET @queryDynamicColumnsDec= LEFT(@queryDynamicColumnsDec,LEN(@queryDynamicColumnsDec)-1)
SET @queryDynamicColumnsLst= LEFT(@queryDynamicColumnsLst,LEN(@queryDynamicColumnsLst)-1)
SET @queryDynamicColumnsLstDec= LEFT(@queryDynamicColumnsLstDec,LEN(@queryDynamicColumnsLstDec)-1)
SET @queryDynamicColumnsDecAvoidNULL = LEFT(@queryDynamicColumnsDecAvoidNULL,LEN(@queryDynamicColumnsDecAvoidNULL)-1)

SET @queryDynamicTable = 'DECLARE @dynamicTable TABLE('+REPLACE(@queryDynamicColumnsDec,'@','')+')'


DECLARE @sql1 NVARCHAR(MAX) =
'DECLARE '[email protected]+CHAR(13)+
@queryDynamicTable+CHAR(13)+
'OPEN query_cursor
FETCH NEXT FROM query_cursor INTO '[email protected]+'
WHILE(@@FETCH_STATUS&lt;&gt;-1)
BEGIN
INSERT INTO @dynamicTable('+REPLACE(@queryDynamicColumnsDec,'NVARCHAR(MAX)','')+') VALUES ('[email protected]+')
FETCH NEXT FROM query_cursor INTO '[email protected]+'
END

SELECT RowId,ColumnName,NULLIF(Value,'''[email protected]+''')[Value] FROM (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1))[RowId],'+ @queryDynamicColumnsDecAvoidNULL+' FROM @dynamicTable
)tblBase
UNPIVOT
(
Value FOR ColumnName IN ('+REPLACE(@queryDynamicColumnsDec,'NVARCHAR(MAX)','')+')
)tblUnPivot
ORDER BY RowId
'

PRINT @sql1
EXEC sp_executesql @sql1

IF CURSOR_STATUS('global','query_cursor')&gt;=-1
BEGIN
DEALLOCATE query_cursor
END

END TRY

BEGIN CATCH
SELECT 1 RowId,'Error'ColumnName,ERROR_MESSAGE()[Value]

IF CURSOR_STATUS('global','query_cursor')&gt;=-1
BEGIN
DEALLOCATE query_cursor
END
END CATCH

Sunday, April 21, 2019 - 8:24:08 AM - Laeeq Hamid Back To Top

Hi Debra,

Thanks for your compliments. query_cursor needs to be a global cursor because it gets executed in a dynamically created query after its declaration. You can use the way cursor is defined as mentioned in the article as or you might have to use it the way John has mentioned in his comments below to specifically declare it as a global cursor. I don't think query_cursor will create an issue in this case, because it remains within the scope of a user connection and this cursor is deallocated before reaching end of the code. If you still come across any issues then please feel free to provide your comments which might help improving this functionality.

Kind Regards,
Laeeq Hamid


Friday, April 19, 2019 - 6:45:33 PM - Debra Back To Top

Thank you, Laeeq, this is truly amazing!

Because I am not too familiar with cursors, can you tell me if "query_cursor" is a global variable, and if another process used "query_cursor", would there be issues?  If so, is there a way to use a local cursor variable instead?

Thanks again!

: Debra


Thursday, April 18, 2019 - 9:48:41 AM - Adam Back To Top

 Hi Laeeq,

Thanks for the response. Thats great news :) ill check back in a couple of weeks. Thanks again for a well writen guide :)


Thursday, April 18, 2019 - 7:45:32 AM - Laeeq Hamid Back To Top

Hi Adam,

Thanks for your appreciation. That's because of the first version of main SQL query I wrote does not accept EXEC function calls. I have a plan of updating the query by using adding a linked server call which might be helpful. Basically, there is a need to re-write/test the next version of this main query to see whether there is some improvement or not. If you follow up this article in a couple of weeks’ time then I might have done something by then.

Kind Regards,
Laeeq Hamid


Wednesday, April 17, 2019 - 6:44:17 AM - Adam Back To Top

Hi Laeeq,

Thanks for the very interesting article and well put together tutorial. Im attempting to actually use this in conjuction with a stored procedure. eg in the SQLText i was trying to enter:

EXEC dbo.SOME_STORED_PROCEDURE @PARAMETER = 'SOMETHING'

but i get the following error:

'Incorrect Syntax near the word EXEC'

any ideas?


Wednesday, January 30, 2019 - 4:07:17 AM - Vijesh V Back To Top

 Hi,

Its a very helpful topic. Great...!!!


Wednesday, November 28, 2018 - 11:38:55 PM - Laeeq Hamid Back To Top

Hi John,

Thank you so much for your comments and thanks for sharing the code.

Kind Regards,
Laeeq Hamid


Wednesday, November 28, 2018 - 12:22:43 PM - John Back To Top

Very nice.

For me, I needed to specifially declare the cursor as GLOBAL...

SET @sqlQuery = 'DECLARE  query_cursor CURSOR GLOBAL FOR '+@sqlQuery

Wednesday, November 28, 2018 - 2:28:38 AM - Laeeq Hamid Back To Top

Hi Roman,

I have tested this solution on SQL Server 2016, 2012 and 2008R2. I reckon 2014 should be fine as well.

Kind Regards

Laeeq Hamid


Tuesday, November 27, 2018 - 3:27:14 PM - Roman Peralta Back To Top

Hi Laeeq:

I saw the solution was designed over SQL Server 2016. It could be done over SQL Server 2014 too?

Best regards


Friday, November 16, 2018 - 2:10:38 PM - Laeeq Hamid Back To Top

Hi Endrit,

Would you be able to tell me what SQL query you trying to pass as a paraneter. It will be great if you could  remove any sensitive infirmation from your query before sharing it.

Thanks

Laeeq


Thursday, November 15, 2018 - 12:29:12 PM - Endrit Back To Top

I have a problem with Cursor showiing me error like :

The variable '@ResultSet' does not currently have a cursor allocated to it.


Thursday, November 08, 2018 - 5:55:57 AM - Laeeq Hamid Back To Top

Further to my previous comments

Prevention from SQL injection has been discussed widely and there are some good articles on prevention and recovery from SQL injection available at reputable websites including MSSQLtips.com.

Here are a few of them

https://www.mssqltips.com/sqlservertip/3637/protecting-yourself-from-sql-injection-in-sql-server--part-1/
https://www.mssqltips.com/sqlservertip/3638/protecting-yourself-from-sql-injection-in-sql-server--part-2/
https://www.mssqltips.com/sqlservertip/1559/recover-from-a-sql-injection-attack-on-sql-server/


Also, there are TSQL codes available to verify if an SQL query contains SQL injection or not.

Thanks
Laeeq



Wednesday, November 07, 2018 - 7:12:48 AM - Laeeq Hamid Back To Top

Hi Tibor, Olu & Adrian


Thanks for your appreciation. With regards to security, the first is that reporting user should have read-only access to the database, they should not have access to updating or deleting data. secondly, I would create an access list and verify authorised users before letting them run a dynamic SQL report.


Thanks
Laeeq


Tuesday, November 06, 2018 - 9:16:46 AM - Adrian Hernandez Back To Top

 Hello,

Very good article. This can be very useful, but, as others have mentioned there are security concerns that need to be addressed. And even after modifying underlying code the fact that it runs Dynamic SQL is always a concern. Dynamic SQL in my experience should rarely be used because of potential security issues.


Tuesday, November 06, 2018 - 6:09:38 AM - Olu Back To Top

 Hi

Thanks for the article, this would have been heaven sent as I am looking to implement a solution like this. However it does not prevent SQL injection attacks. There is nothing stopping a user from doing something like this: 

   DECLARE @sqlQuery NVARCHAR(MAX) ='Select top 1 contact_id from contact;  Update contact set address1 = ''MyAdress1'' where contact_id = 0'

   SET @sqlQuery = 'DECLARE  query_cursor CURSOR FOR '[email protected]

   EXEC sp_executesql @sqlQuery

 

Any thoughts \ suggestions on this?

 

Thanks

 


Tuesday, November 06, 2018 - 3:55:52 AM - Tibor Nagy Back To Top

Hi,

Interesting solution but I have some doubts about the security of this approach...


Learn more about SQL Server tools