Can you pass parameters to a View?

[Woody Splawn]

I am using SQL Server 2000 as a back-end to a VS.net Client/Server app. In
a certain report I use a view as part of the query spec. For the view, at
present, I am querying for all the records in the table. But I am wondering
if there is a way, at runtime, to pass values to the View (like start date
and end date) so that I don't have to return every record in the table of my
view. If I can't pass a parameter, perhaps I can create a view, a-fresh,
with the parameters, each time I run the report. If I can, I don't know
what the syntax might be under Visual Studio Code nor where to go to find
it.

Could somone point me in the right direction?

 

[Justin Weinberg]

I'm not sure which reporting engine you are using, but it can be done with a
stored procedure for sure if your engine allows this. You can also make
stored procedures that use views, though you may not need the view anymore
with the SP.

 

[Yan-Hong Huang[MSFT]]

Hello Woody,

Thanks for posting in the group.

I noticed that you mentioned report in the post. Did you mean crystal
report?

Generally speaking, if we want to return specific rows from a DB, we could
use a select statement or a stored procedure in .NET programming, and then
use the returned records as the data source.

For an example, we could code like:

DataSet ds = new DataSet();
SqlConnection cn = new SqlConnection("server=myServer;user
id=myUID;password=a"b;database=northwind");
SqlDataAdapter da = new SqlDataAdapter("select * from customers where
customerid = ***",cn);
da.Fill(ds, "customers");

Also, there is a good document named ".NET Data Access Architecture Guide"
in MSDN. This document provides guidelines for implementing an
ADO.NET-based data access layer in a multi-tier .NET application. It
focuses on a range of common data access tasks and scenarios and presents
guidance to help you choose the most appropriate approaches and techniques.
We could reach it at
http://msdn.microsoft.com/library/en-us/dnbda/html/daag.asp?frame=true.

Does that answer your question?

Best regards,
Yanhong Huang
Microsoft Online Partner Support

Get Secure! ¨C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
!From: "Woody Splawn" <[email protected]>
!Subject: Can you pass parameters to a View?
!Date: Thu, 13 Nov 2003 10:29:18 -0800
!Lines: 14
!X-Priority: 3
!X-MSMail-Priority: Normal
!X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
!X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
!Message-ID: <#[email protected]>
!Newsgroups: microsoft.public.dotnet.languages.vb
!NNTP-Posting-Host: 168.158-60-66-fuji-dsl.static.surewest.net 66.60.158.168
!Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
!Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.languages.vb:156592
!X-Tomcat-NG: microsoft.public.dotnet.languages.vb
!
!I am using SQL Server 2000 as a back-end to a VS.net Client/Server app. In
!a certain report I use a view as part of the query spec. For the view, at
!present, I am querying for all the records in the table. But I am
wondering
!if there is a way, at runtime, to pass values to the View (like start date
!and end date) so that I don't have to return every record in the table of
my
!view. If I can't pass a parameter, perhaps I can create a view, a-fresh,
!with the parameters, each time I run the report. If I can, I don't know
!what the syntax might be under Visual Studio Code nor where to go to find
!it.
!
!Could somone point me in the right direction?
!
!
!
!

 

[Woody Splawn]

Thanks for responding

I noticed that you mentioned report in the post. Did you mean crystal
report?

I am using Active Reports

we could use a select statement or a stored procedure

Understood. I am already doing what you suggested. In one of my select
statments I user 4 tables and a view to produce the answer set. My question
has to do with whether I should pair-down the view (by selection criteria
that is indegeneous to the view) prior to running the big query. I have a
message posted on the SQL Server forum on this as well. Just trying to get
the best times (speed) possible.

I appreciate your and Microsoft's attention.

Woody

 

[Yan-Hong Huang[MSFT]]

Hi Woody,

I am glad to be of assistance. Based on my experience, stored procedure
often improves performance. So I suggest you use it as much as possible.

Thanks again for participating the community.

Best regards,
Yanhong Huang
Microsoft Online Partner Support

Get Secure! ¨C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Joe \Nuke Me Xemu\ Foster]

I am glad to be of assistance. Based on my experience, stored procedure
often improves performance. So I suggest you use it as much as possible.

The better to lock us in to SQL Server, right?

 

[Yan-Hong Huang[MSFT]]

Hi,

I think it is a guideline even when we are programming on other DBs. I have
no much experience on other kinds of DBs. But they may also have some
techniques on DB sides which could improve the performance of program.
Please correct me if I have something wrong here.

From MSDN, we could see:

You should use stored procedures, instead of embedded SQL statements, for a
number of reasons:

Stored procedures generally result in improved performance, because the
database can optimize the data access plan used by the procedure and cache
it for subsequent reuse.

Stored procedures can be individually secured within the database. A client
can be granted permissions to execute a stored procedure, without having
any permissions on the underlying tables.

Stored procedures result in easier maintenance, because it is generally
easier to modify a stored procedure than it is to change a hard-coded SQL
statement within a deployed component.

Stored procedures add an extra level of abstraction from the underlying
database schema. The client of the stored procedure is isolated from the
implementation details of the stored procedure and from the underlying
schema.

Stored procedures can reduce network traffic, because SQL statements can be
executed in batches rather than sending multiple requests from the client.

Thanks very much.

Best regards,
Yanhong Huang
Microsoft Online Partner Support

Get Secure! ¨C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.