Pass a parameter in SSRS to insert into a SQL Server query to update a table

Question

I want to know if its possible to write a SSRS report that prompts for a user name and then inserts that parameter into the SQL query and runs it.

Or create this through SSIS?

UPDATE UserDatabase 
SET Log_Number = '0' 
WHERE UserID = 'user2'

UPDATE UserDatabase 
SET Machine_Name = NULL 
WHERE UserID = 'user1'

Answer 1

This can most definitely be done is SSRS. You can do it two ways. One is make a parameter in your SQL Statement

DECLARE @User2 VARCHAR(30), @User1 VARCHAR(30)

UPDATE UserDatabase
SET Log_Number = '0'
WHERE USerID = @User2

UPDATE USerDatabase
SET Machine_Name = NULL
WHERE UserID = @User1

This way it will create the parameters in the parameters tab for you after you add the sql statement to the report.

You can also go to the parameters tab and create the parameters you want to use. Then right click on your Data Set and inside the properties you can add your parameters and filter the result set on the parameters. The second way is a little harder though.

I am not sure if you can use UPDATES or not in SSRS I have never tried it and used it just for reports. IF you need to do UPDATES I would use SSIS and i know you can pass in parameters there too.