SQL Server Question variables...?

Question

I am trying to create a select statement that will select data based on criteria that may or may not be given:

Select * from Table1
Where Column1 = TextBox1
and Column2 = TextBox2
and Column3 = TextBox3

The user however may not enter data into all TextBoxes which will result in the sql statement either failing or checking to see if that field contains "". How can I create one procedure that will select whether or not data is entered?

Ok that will definately work. I appreciate your help...I was hoping for the lazy mans way of possibly passing a value that SQL would interpret as "ANY" such as:
where Column1 = *

but I guess I gotta do it the long way...Alright well Thanks again for your help!

Answer 1

-- try this. using coalesce will give u a quick and elegant solution

Select * from Table1
Where Column1 = coalesce(TextBox1,column1)
and Column2 = coalesce(TextBox2, column2)
and Column3 = coalesce(TextBox3, column3)

Answer 2

You already have the answer. If any of the variables contains a Null value however, the results will always be a blank dataset as a Null isn't something and it ain't nutt'in. Create some variables and initialize them with ''. Then set each = to its respective textbox value. If the textbox value is '' nothing changes or course. Once you have all of the variables set with the textbox values, test each one to see if it equals '', if so then do not include it in the sql statement. With the variables that contain a value, create a SQL string variable, and concantenate the SQL statement together using only the variables that contain values. Then do a close - add - open on the SQL query. Example: QryTest1.Close
QryTest1.Add SQLSTRING QryTest1.Open

Or you can pass the SQLSTRING as a parameter.

Answer 3

Set up a query string
MyQueryString = "Select * from Table1"
MyQueryString = MyQueryString & "Where Column1 = " & TextBox1 & "
if textBox2.text <> "" then
MyQueryString = MyQueryString & "and Column2 = TextBox2"
end if
if textBox2.text <> "" then
MyQueryString = MyQueryString & "and Column3 = TextBox3"
end if

That way it only adds on the 'and' statements if there is data in the text boxes

Answer 4

because sq. 2008 has been out for a lengthy time period i could stay remote from 2000 and concentrate on 2005 and 2008. do not assume any keep could have a unmarried version of the DB round. we are at the moment deploying 2008 for brand new platforms we setup and present 2005 platforms will be migrated over the subsequent year. i does no longer waste any time on 2000 until eventually there's a particular activity you're going for that makes use of it.