Parameter Views in SQL SERVER 2005

[jjc3397]

I am trying to write a view or query using Paramters.

I want the user to be prompted to enter a License Type and then be prompted to enter a county number. I want data to appear based on these parameters entered in by the user in paramter boxes as in Access. I know the SQL SERVER 2005 does this a different way. I am learning and wish to understand this major concept before moving forward.

Select StrLicType, License#, and County#
FROM TBLLicense
Where STRLICTYpe = "010", "020" , "030", and "040" and County# = "35";

I know the above SQL Statement will work this way with me listing the paramters, but I want the user to enter the LIcType and County in when prompted.

 

[jebenson]

So you are writing a VB .NET application that will prompt for the parameters, pass them to a SQL Server stored procedure, and display the results to the user?

Here's how to do this:

SQL Server stored proc:

CREATE PROCEDURE [dbo].[spSelectLicenses]
@LicType varchar(3),
@County varchar(3)

AS
Select StrLicType, [License#], [County#]
FROM TBLLicense
Where STRLICTYpe=@LicType and County#=@County
GO

In VB:

Dim Conn As SQLConnection
Dim da As SQLDataAdapter
dim ds As DataSet

Conn = New SQLConnection("<connection string...>")

da = New SqlDataAdapter

da.SelectCommand = New SqlCommand("spSelectDS", Conn)

With da.SelectCommand
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@LicType", SqlDbType.VarChar)
.Parameters.Add("@County", SqlDbType.VarChar)
.Parameters("@LicType").Value = "010"
.Parameters("@County").Value = "35"
End With

ds = New DataSet
da.Fill(ds)

There is a drawback to doing this, which is that it's not all that easy to pass a list of parameters (e.g., "010", "020" , "030", and "040" for LicType). There are some ways to do this, as detailed in this link: faq183-3979

Another way that will probably be much simpler is to skip the stored procedure and just do your select straight from the VB code. This way you can build your select SQL and include as many parameters as you want, without having to jump through any hoops to get them into a stored proc's parameters.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira