Microsoft SQL Server Reporting Services is an excellent and easy-to-use platform for creating, viewing, managing, and subscribing to reports of all shapes and sizes.
Technically, this platform is meant for nothing more than reports. I mean, it’s true that reporting is a huge field and you can do all kinds of cool stuff with it, but it’s still just reporting – or in other words: Viewing data.
However, it’s also possible to use this platform for updating data. Whether it’s deleting items, updating them, or even inserting new records.
It’s especially useful when your organization doesn’t have skilled programmers that can create a pretty-looking graphical user interface (GUI). Or when there are budget and / or time restrictions.
The key to this special usage of Reporting Services is the Data Sets of type “Stored Procedure”. In SQL Server Reporting Services, one way of getting data for your report is executing a stored procedure in your database.
However, a stored procedure (as you probably know) can do a lot more than just querying data. Technically, when you use stored procedures in SSRS, all they need to do is:
- Receive parameter values.
- Do some processing.
- Return a set of data.
That second step can be absolutely anything. It can be an insertion of new data, updating or deleting, and even some complex combination of everything.
Actually, you don’t really need a stored procedure to do this. You can also use the Text data set and write all of your logic directly as a T-SQL script. But in my opinion, it’s not so comfortable, and a lot more cumbersome to work like that, especially when you need to debug your logic, or when the same logic is used in several reports.
Also, it’s important to remember that your stored procedure must return a single result set at the end. Don’t forget this is Reporting Services we’re dealing with, and it expects to receive a single result set for the data it needs to display.
Updating Rows
For example, here is a sample procedure that I wrote which transfers all the employees from one manager to another (in the AdventureWorks database):
Personally, I like implementing this using what I call an “output temporary table”. The idea is that throughout your logic, you may have several steps, and you want to know the outcome of each of these steps (success or failure, number of rows affected etc.). To do so, I create a temporary table (best if it’s a variable table), and at the success or failure of each step, I insert a record with a message in that table. At the end, I simply SELECT the entire table and display it in the report.
CREATE PROCEDURE MoveEmployeesFromManagerToManager @SourceManagerID INT, @DestinationManagerID INT AS DECLARE @RCount INT DECLARE @Output AS TABLE ( Msg NVARCHAR(MAX) ) BEGIN TRY INSERT INTO @Output VALUES( N'Moving employees from ManagerID ' + CONVERT(nvarchar(50), @SourceManagerID) + ' to ManagerID ' + CONVERT(nvarchar(50), @DestinationManagerID) + '...' ) BEGIN TRANSACTION UPDATE Employees SET ManagerID = @DestinationManagerID OUTPUT N'Moved EmployeeID ' + CONVERT(nvarchar(50), INSERTED.EmployeeID) INTO @Output FROM HumanResources.Employee AS Employees WHERE ManagerID = @SourceManagerID; SET @RCount = @@ROWCOUNT; IF @RCount > 0 BEGIN INSERT INTO @Output VALUES( N'Moved ' + CONVERT(nvarchar(50), @RCount) + ' employee(s).') END ELSE BEGIN INSERT INTO @Output VALUES(N'No employees were updated.') END COMMIT TRANSACTION END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; INSERT INTO @Output VALUES(N'CRITICAL ERROR: ' + ERROR_MESSAGE()) END CATCH SELECT * FROM @Output
As you can see, before performing the actual update, I put a message into the @Output table informing of the operation. Then I use the UPDATE…OUTPUT…INTO syntax to save in the @Output table all the employees that were updated. Finally, I insert another message in @Output informing of the success or failure of the operation. Most importantly, note that the entire logic is enveloped in a TRY..CATCH block, and in the CATCH segment I put into the @Output table the error message that occurred. When you create a Reporting Services report which uses this procedure, you specify this procedure as the source of the data set, and add a table which will display the contents of the query (from the @Output table).
Deleting Rows
Here is another sample where I have two reports:
- A report displaying the contents of the DatabaseLog table.
- A report which deletes a record from DatabaseLog table.
The first report will feature a button which will navigate me to the second report, and on the way go through the DatabaseLogID which I want to delete. The second report will use a procedure which will delete the record with the provided ID, and display a message about it. Here is the script for its procedure:
CREATE PROCEDURE DeleteDatabaseLogRow
@DatabaseLogID INT
AS
DECLARE @Output AS TABLE
(Msg NVARCHAR(MAX))
BEGIN TRY
INSERT INTO @Output
VALUES ('Deleting record with DatabaseLogID ' + CONVERT(varchar(50),@DatabaseLogID))
DELETE FROM DatabaseLog
WHERE DatabaseLogID = @DatabaseLogID
IF @@ROWCOUNT > 0
INSERT INTO @Output
VALUES('Successfully deleted record.')
ELSE
INSERT INTO @Output
VALUES('Error: No such record found.')
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
INSERT INTO @Output
VALUES(N'CRITICAL ERROR: ' + ERROR_MESSAGE())
END CATCH
SELECT * FROM @Output
Inserting Data
Another example (which I will not demonstrate here), is performing an INSERT to a table, where the report parameters represent the values you wish to enter into each column.
XP_CMDSHELL
Another interesting example would be to execute something that’s even outside of SQL Server. That could be a standalone EXE file, or some DOS command that copies or deletes files, etc.. These commands will be executed using XP_CMDSHELL (assuming it’s enabled and your login has the correct permissions). Remember that XP_CMDSHELL returns a query which contains the resulting output from the operation. This output can be saved in our @Output table and then displayed in the report.
For example, suppose we have a table with file or folder paths, and we create a report where we move these files or folders to a new location.
For this you’d need to be familiar with the basic CMD commands such as XCOPY, DEL, MOVE and possibly DIR. But that is beyond the scope of this blog post. Don’t worry though, there’s plenty of resources on the internet for this.
Here is a sample of using XP_CMDSHELL in conjunction with our @Output table in order to move an entire folder from one location to another. It’s pretty straightforward, so I’ll leave the rest to you:
SET @CMD = 'xcopy /E /C "' + @Source + N'*.*" "' + @Destination + '*.*"' INSERT INTO @Msg EXEC xp_cmdshell @CMD
Conclusion
As you can guess, the possibilities are almost endless. The only problem I could think of is when you need to insert a large amount of rows into some table. Using the report parameters is convenient for providing column values of a single row, but a group of rows is another matter altogether. At the very least, you could use Microsoft Access linked to a SQL Server to easily enter groups of rows in a grid view. Theoretically, you can use SSIS to import Excel files with multiple records to insert, but that’s also outside our scope of Reporting Services. Other than that, and the built-in graphical interface of SQL Server Management Studio, I don’t know a simpler way to do this (without programming your own GUI obviously).
Thoughts? More interesting ideas? Post them in the comments!
This article was originally published by Eitan on February, 2012 in www.madeiradata.com