Run stored procedures with report data as input parameters

How can I run stored procedures with report data as input parameters?

Stored procedures are one of SQL’s most powerful tools to update, insert or delete data in your database. Since most reports are designed to provide Business Intelligence to users, there could be an action the user needs to take based on the presented data. Wouldn’t it be great if the user could take that action without leaving the report?
This post describes how to run a stored procedure directly from your report with row data as input parameters.
I will use a simple example with a custom table and a small stored procedure so you get the picture. Soon you will discover that with these steps and a creative mind, the options are near limitless for taking direct actions in your database! Think about checklist reports or scheduling reports.
1) Create a custom table in your database (you can also use existing tables if you know what you’re doing)
Create TABLE [dbo].[AdvancedSSRS_CusOrd]
([ID] [int] IDENTITY(1,1) NOT NULL,
[Account] [Varchar](50) NOT NULL,
[OrderNo] [Varchar](50) NULL
) ON [PRIMARY]
GO
2) Create a stored procedure that Inserts new records in the table created in step 1
SET ansi_nulls ON 
go 

SET quoted_identifier ON 
go 

CREATE PROCEDURE Advancedssrs_insertintoadvancedssrs_cusord 
@Account VARCHAR(50), 
@OrderNo VARCHAR(50 

AS 
  BEGIN 
      SET nocount ON; 

      –Check if record already exists 
      IF EXISTS (SELECT NULL 
                 FROM   advancedssrs_cusord 
                 WHERE  account = @Account 
                        AND orderno = @OrderNo) 
        — if exists 
        BEGIN 
            UPDATE advancedssrs_cusord 
            SET    account = @Account, 
                   orderno = @OrderNo 
            WHERE  account = @Account 
                   AND orderno = @OrderNo 
        END 
      — if record is new 
      ELSE 
        BEGIN 
            INSERT INTO advancedssrs_cusord 
                        (account, 
                         orderno) 
            VALUES      (@Account, 
                         @OrderNo) 
        END 
  END 

go 
Since I don’t want to get multiple records of the same Customer/Order combination I included a check to see if the record exists. If it does, the stored procedure will overwrite the row with the same values. If it doesn’t, the stored procedure will add an extra row.
3) Add report parameters that will serve as input parameters for the stored procedure
In this example I want to mark some orders as “special”, so I will add a parameter for @Account and @OrderNo. Make sure to allow blank values.
 
4) Add a dataset that exectutes the stored procedure created in step 2
The dataset checks if the report parameter(s) are not NULL (which is how the report runs by default). Only if the parameters have values, the stored procedure will be executed.
If you use data from your custom table in your report, you want to make sure this dataset is the first dataset the report will run. Unfortunately you cannot move the position of the dataset, so if you have an existing dataset, copy the query, delete the dataset and recreate it.
5) Add a column in your Tablix to launch the stored procedure
Insert text or an image and go to its properties. Browse to the Action tab. Choose to “Go to report” and choose the report you are working on. Add parameters to pass on so you pass the value in a row to the report parameters.
You see where this is going? Once the user cicks the image/text, the report will be launched again (refreshed) but this time the parameters are not blank. This will trigger the dataset created in step 4 to exectute the stored procedure. In my scenario a row will be inserted in the table created in step 1.
6) Test your report
7) Hide the report parameters
Annunci

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...