MS SQL SERVER Queries/views with user input

[ddebecke]

Hi,

I would like to create some queries (actually, preferrably views) in ms sql server that allow for user input. The reason why I want this, is because I am trying to generate some reports about my data using excel (for example periodical reports, in which case I would have to give the queries a start- and enddate), but I would rather keep all the complex database-stuff inside sql server.

Can anyone tell me if this is possible?

Thanks

[Krzemo]

Can anyone tell me if this is possible?

Yes - using Stored Procedures.

[davide++]

Hi all.
Generally using views to "export" data from a database is a good way to manage reports.
If you're going to use Excel for data analysis, you may be write a Excel-macro that connects to database, extracts data and puts them in the sheets (I've done this yet, with a Oracle database).

@Krzemo. A view is a "stored-query"; why do one should use stored procedures?

[Krzemo]

A view is a "stored-query"; why one should use stored procedures?

Since stored procedures accepts parameters and it is faster and safer than an ad hoc query that use view.

And a view sometimes leads to strange errors when U alter some underlying tables without refreshing view definition.


Krzemo.

[davide++]

Ha, OK.
You meant stored procedure as alternative for view; I thought you spoke about stored procedure as a way to create the views, and this was quite strange for me (nevertheless one can use a stored procedure to write records in a table, and then using the table as a view).

I agree.
Furthermore, a stored procedure may be easier to develop rather than a complex query.