SQL Server 2005 Stored procedure write report Example

I heard that Ms SQL 2005 with the report function, so I tested a bit, but also relatively easy to use, so I recorded a stored procedure to write a report example. Since there is no voice in the test process, I am here to explain.

The stored procedures used are as follows (in the video I finally changed the stored procedure):

The database used is the Northwind database for Ms SQL 2000, and the table is orders.

if exists(select * from sysobjects where name='procTest' and xtype='P')
drop proc procTest
go
create proc procTest
@CustomerId varchar(20)
as
if @CustomerId='*'
select * from Orders
else
select * from Orders where CustomerID=@CustomerId

Can be tested with exec proctest ' vinet ' or exec proctest ' * '

Then build the database report project

1, the establishment of data sources

2, the establishment of new reports

3, set up a dataset, here I used a special way, because the report design interface needs to list the fields, so I will save the process needs to output the field SELECT statement first run in the dataset, then get all the fields of the recordset, and do not add it yourself.

4, the establishment of the report Parameters CustomerID, set the default value of *, and stored procedures to determine the sentence consistent.

5. , change the statement in the data: = "exec proctest '" & parameters! Customerid.value & "'"

6, basically complete.

In the demo do not know how to press F5 to appear in the report is not realistic, but in the preview is normal, probably the principle can be understood. About the report anonymous browsing the problem is introduced on the Internet, here is not much to say.