Moonshadow


I have a column YearGiven (smallint, null). I need to be able to use a WHERE clause that will include all years. Something like this:

SELECT * FROM Documents WHERE YearGiven = *

I realize if I eliminate the WHERE clause entirely this will produce the desired result. However in my VB app my Select statement will have to take into account that the YearGivenTextBox used in the Full Text Search may be empty. This is not a problem if I can assign a value to the empy textbox variable that will include all years.




Re: Inclusive WHERE expression

Konstantin Kosinsky


If you use @YearGivenTextBox parameter, you could use following code:

Code Snippet

SELECT * FROM Documents WHERE YearGiven = @YearGivenTextBox OR @YearGivenTextBox IS NULL

--Also you could try

SELECT * FROM Documents WHERE YearGiven = @YearGivenTextBox OR @YearGivenTextBox = ''

If @YearGivenTextBox not empty first part work, else second and SQL Server returns all rows.






Re: Inclusive WHERE expression

xrayb

This would work:

SELECT * FROM Documents WHERE YearGiven IN (SELECT YearGiven FROM Documents)

You could also have a look at dynamic sql to build you sql string on-the-fly at run time and pass throught he parameters you need , here's a fantastic article on how to do that:

http://www.sommarskog.se/dynamic_sql.html

Ray






Re: Inclusive WHERE expression

Manivannan.D.Sekaran

Don¡¯t use the logical expression against the variables on WHERE clause, it will force the engine to use Index Scan (even there is a possibility to use Index Seek). Always better check with if statement,

Don¡¯t try to reduce the number of lines, always give the preference to the performance,

Code Snippet

if @yeargiventextbox is null or @yeargiventextbox = '' -- isnull(@yeargiventextbox ,'') = ''

select * from documents

else

select * from documents where yeargiven = @yeargiventextbox






Re: Inclusive WHERE expression

Moonshadow

Thanks for suggestions. You have all given me some things to think about (especially Ray's link to the article on dynamic SQL). I am reconsidering my original strategy for how to work with the WHERE clause.

Manivannan, how would I implement the IF-ELSE code snippet you provided in my Visual Basic app Would it be embedded in the main SELECT statement, or set apart by using WHERE 1 = 1 in the SELECT statement





Re: Inclusive WHERE expression

Moonshadow

Following up on the link provided above by Ray, I have been researching the concept of Dynamic SQL:

Dynamic Search Conditions in T-SQL
An SQL text by Erland Sommarskog, SQL Server MVP.

http://www.sommarskog.se/dyn-search.html


Although it is a little complex for me with my current understanding of T-SQL, it does seem to address some of the problems I am facing and may provide a good learning experience (if nothing else.) He is filtering with 12 parameters. In my application I will have 9 (6 comboboxes and 3 textboxes) plus my basic WHERE CONTAINS full text search. So this may be comparable.

QUESTION 1: Based on the title of this thread, my initial question is: What does the expression WHERE 1=1 mean Sommarskog is using his version of the Northwind DB and there is no column 1 that I can see. Is this a T-SQL convention for appending parameters to the main query Or what

QUESTION 2: Does this seem like a good approach for me based on the info I have provided above

Thanks again for your patience and expertise in explaining these difficult concepts (for me) and providing workable options.

Here is Sommarskog's code example for the Dynamic Search stored procedure cited above:


Code Snippet

CREATE PROCEDURE search_orders_1
@orderid int = NULL,
@fromdate datetime = NULL,
@todate datetime = NULL,
@minprice money = NULL,
@maxprice money = NULL,
@custid nchar(5) = NULL,
@custname nvarchar(40) = NULL,
@city nvarchar(15) = NULL,
@region nvarchar(15) = NULL,
@country nvarchar(15) = NULL,
@prodid int = NULL,
@prodname nvarchar(40) = NULL,
@debug bit = 0 AS


DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)


SELECT @sql =
'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
c.CustomerID, c.CompanyName, c.Address, c.City,
c.Region, c.PostalCode, c.Country, c.Phone,
p.ProductID, p.ProductName, p.UnitsInStock,
p.UnitsOnOrder
FROM dbo.Orders o
JOIN dbo.[Order Details] od ON o.OrderID = od.OrderID
JOIN dbo.Customers c ON o.CustomerID = c.CustomerID
JOIN dbo.Products p ON p.ProductID = od.ProductID
WHERE 1 = 1'


IF @orderid IS NOT NULL
SELECT @sql = @sql + ' AND o.OrderID = @xorderid' +
' AND od.OrderID = @xorderid'


IF @fromdate IS NOT NULL
SELECT @sql = @sql + ' AND o.OrderDate >= @xfromdate'


IF @todate IS NOT NULL
SELECT @sql = @sql + ' AND o.OrderDate <= @xtodate'


IF @minprice IS NOT NULL
SELECT @sql = @sql + ' AND od.UnitPrice >= @xminprice'


IF @maxprice IS NOT NULL
SELECT @sql = @sql + ' AND od.UnitPrice <= @xmaxprice'


IF @custid IS NOT NULL
SELECT @sql = @sql + ' AND o.CustomerID = @xcustid' +
' AND c.CustomerID = @xcustid'


IF @custname IS NOT NULL
SELECT @sql = @sql + ' AND c.CompanyName LIKE @xcustname + ''%'''


IF @city IS NOT NULL
SELECT @sql = @sql + ' AND c.City = @xcity'


IF @region IS NOT NULL
SELECT @sql = @sql + ' AND c.Region = @xregion'


IF @country IS NOT NULL
SELECT @sql = @sql + ' AND c.Country = @xcountry'


IF @prodid IS NOT NULL
SELECT @sql = @sql + ' AND od.ProductID = @xprodid' +
' AND p.ProductID = @xprodid'


IF @prodname IS NOT NULL
SELECT @sql = @sql + ' AND p.ProductName LIKE @xprodname + ''%'''


SELECT @sql = @sql + ' ORDER BY o.OrderID'


IF @debug = 1
PRINT @sql


SELECT @paramlist = '@xorderid int,
@xfromdate datetime,
@xtodate datetime,
@xminprice money,
@xmaxprice money,
@xcustid nchar(5),
@xcustname nvarchar(40),
@xcity nvarchar(15),
@xregion nvarchar(15),
@xcountry nvarchar(15),
@xprodid int,
@xprodname nvarchar(40)'


EXEC sp_executesql @sql, @paramlist,
@orderid, @fromdate, @todate, @minprice,
@maxprice, @custid, @custname, @city, @region,
@country, @prodid, @prodname





Re: Inclusive WHERE expression

Moonshadow

OK, Question # 1: What does WHERE 1 = 1 mean

Here is what I found:

"If you're building a WHERE clause on the fly, and you don't know if there are any more expressions in the WHERE clause, then starting with 1=1 insures that you'll create a valid WHERE clause and the SELECT won't blow up. I don't recommend it but it works and it's quick."


"It's a standard way to have a "where" clause that it's always true."

"It allows the developers to not worry ... Normally used in dynamically generated SQL."

Question # 2: Is Dynamic SQL approach best for me

Still don't know yet. I am trying a more standard Sproc approach and have gotten some of it to work. I can query in Visual Basic using a sprock with 6 parameters. I don't know how to include my Full Text Search parameter into the sproc. I don't know how to use IF-ELSE when the parameter value is NULL (the TextBox is empty or Combobox is unselected). Here is my sproc and Visual Basic code that works:

Code Snippet

CREATE PROC usp_Advanced_Search

@doctype nvarchar(10) = NULL,

@year varchar(6) = NULL,

@sex varchar(6) = NULL,

@category nvarchar(10) = NULL,

@agenum smallint = NULL,

@agecat nvarchar(10) = NULL AS

SELECT FullDocuments.FullDocNo, FullDocuments.DocType, Details.Year

FROM FullDocuments

INNER JOIN Details ON FullDocuments.FullDocNo = Details.FullDocNo

WHERE DocType = @DocType AND Year = @Year AND sex = @sex

AND category = @category AND agenum = @agenum AND agecat = @agecat

Code Snippet

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SearchButton.Click

Dim conn As New SqlConnection("Data Source=OFFICE\FULLTEXTSEARCH;Initial Catalog=ECR;Integrated Security=True")

Dim Command As New SqlCommand("usp_Advanced_Search", conn)

Command.CommandType = CommandType.StoredProcedure

Dim SelectedDocType = DocTypeComboBox.Text.ToString

Dim SelectedYear = YearTextBox.Text.ToString

Command.Parameters.Add("@DocType", SqlDbType.VarChar, 10)

Command.Parameters("@DocType").Value = DocTypeComboBox.Text.ToString

Command.Parameters.Add("@Year", SqlDbType.VarChar, 6)

Command.Parameters("@Year").Value = YearTextBox.Text.ToString

Command.Parameters.Add("@Category", SqlDbType.VarChar, 12)

Command.Parameters("@Category").Value = CategoryComboBox.Text.ToString

Command.Parameters.Add("@Sex", SqlDbType.VarChar, 6)

Command.Parameters("@Sex").Value = SexComboBox.Text.ToString

Command.Parameters.Add("@AgeNum", SqlDbType.SmallInt)

Command.Parameters("@AgeNum").Value = AgeNumTextBox.Text.ToString

Command.Parameters.Add("@AgeCat", SqlDbType.VarChar, 8)

Command.Parameters("@AgeCat").Value = AgeCatComboBox.Text.ToString

Dim adapter As SqlDataAdapter = New SqlDataAdapter()

adapter.SelectCommand = Command

Dim ds As New DataSet()

conn.Open()

adapter.Fill(ds)

conn.Close()

DataGridView1.DataSource = ds.Tables(0)

End Sub

I have tried to include a FullTextSearch Parameter in my sproc like this:

Code Snippet

CREATE PROC usp_Advanced_Search3

@doctype nvarchar(10) = NULL,

@year varchar(6) = NULL,

@sex varchar(6) = NULL,

@category nvarchar(10) = NULL,

@agenum smallint = NULL,

@agecat nvarchar(10) = NULL AS

DECLARE @SearchTerm NVARCHAR(100)

SET @SearchTerm ='SearchTextBox.Text'

SELECT FullDocuments.FullDocNo, FullDocuments.DocType, Details.Year

FROM FullDocuments

INNER JOIN Details ON FullDocuments.FullDocNo = Details.FullDocNo

WHERE DocType = @DocType AND Year = @Year AND sex = @sex

AND category = @category AND agenum = @agenum AND agecat = @agecat AND CONTAINS(SectionText, 'SearchTerm')

I then added this code to the VB app:

Command.Parameters.Add("@SearchTerm", SqlDbType.NVarChar, 100)

Command.Parameters("@AgeCat").Value = SearchTextBox.Text.ToString

The VB solution builds successfuly but when I try the Full Text Search I get this error:

Procedure or function usp_Advanced_Search has too many arguments specified.

Any specific suggestions or code would be greatly appreciated.