MS SQL SERVER Queries/views with user input
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
Re: MS SQL SERVER Queries/views with user input
Quote:
Can anyone tell me if this is possible?
Yes - using Stored Procedures.
Re: MS SQL SERVER Queries/views with user input
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?
Re: MS SQL SERVER Queries/views with user input
Quote:
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.
Re: MS SQL SERVER Queries/views with user input
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.