Garth Wells passed on another article to us. This one covers building a dynamic where clause. And the really neat part is that you don't actually need to write dynamic SQL. This is soooo much simpler. One of my first Internet-related projects was a data-driven web site for a commercial real estate firm. One of the features they wanted the site to support was an interface that allowed potential customers (tenants) to search for properties using one or more search criteria. At the time, the only way I knew how to create a dynamic WHERE clause was by using dynamic SQL. With dynamic SQL, you build the SELECT statement based on the search criteria supplied.
Laptop Battery Let's look at an example that shows how this works. Assume you have a table called Customers created with the following statement. CREATE TABLE Customers ( Cus_ID int PRIMARY KEY IDENTITY, Cus_Name varchar(30) NOT NULL, Cus_City varchar(30) NOT NULL, Cus_Country varchar(30) NOT NULL )
According to the indictment, Jones would steal various IBM and Penguin computer servers from Verisign's warehouse in Virginia and sell them to Johnson. Johnson would then sell the servers to several individuals, who would sometimes place them for sale on eBay. As a result of this scheme, the indictment alleges that Jones and Johnson caused Verisign to lose more than $120, 000 worth of computer equipment. In the indictment, Jones and Johnson are charged in three counts with causing the interstate transportation of stolen property, namely IBM 330 and 335 servers, in violation of 18 U.S.C.
Thinkpad The users want to query the table on the Cus_Name, Cus_City and Cus_Country columns independently or in combination. In other words, they want to be able to specify none, one, or more than one criteria and have the resultset filtered accordingly. Using dynamic SQL, the statements (partial) needed to create the query are shown here. DECLARE @SQL varchar(1000) SET @SQL = 'SELECT Cus_Name, Cus_City, Cus_Country FROM Customers ' IF @Cus_Name IS NOT NULL OR @Cus_City IS NOT NULL OR _ @Cus_Country IS NOT NULL SET @SQL = @SQL + 'WHERE ' IF @Cus_Name IS NOT NULL SET @SQL = @SQL + 'Cus_Name = ' + @Cus_Name ... EXEC(@SQL)
Computer memory is the quickest, cheapest, and easiest way to improve the performance of your system. Find RAM memory upgrades for desktops, laptops, servers, and printers all backed by a lifetime warranty and guaranteed compatible with your computer. Shipping is an everyday low price of $1.99! Computer Memory Outlet sells memory compatible with all leading computer manufacturers like Dell, Apple, Compaq, HP, Sony, IBM, Lenovo, and many more.”
Microsoft The final SELECT is a function of the variables used to store the criteria specified by the users and is executed with the EXEC statement.
A notebook computer or also known as laptop computer is a small mobile computer, usually small enough to sit on your lap. Geeks.com offers cheap laptop, laptop computers, and a nice variety of notebook computers for all types of computing needs and budgets. If you are looking for a refurbished computer or cheap laptop, find it first at Geeks.com PriceGrabber User Ratings for Computer Geeks to see ratings Additional Options.
Laptop Computers The dynamic SQL approach will certainly work, but it has two downsides. The first is that it is cumbersome to implement. Building SQL statements in this manner is an error-prone endeavor that takes a lot of time to get right. The second downside has to do with query performance speed. It is impossible for SQL Server's query processor to re-use execution plans produced by dynamic SQL statements. An execution plan is how the database engine actually retrieves the data from the database. When static SQL is used, execution plans can be re-used by different calls to the same statement. This results in faster query processing time because one less step is required to process the query. The COALESCE Function
As a rental organization ICC has kept strong focus on service by following strict quality control procedures and providing 24 hour a day, 7 day a week technical support on every computer rental. Feel secure when renting a computer from iccrents.com. Rent Laptops from Quick, 1000 laptops in stock, HP, CPQ, Dell, IBM, 24x7 Support, Same Day deliveries available nationwide. 20 years of experience.
Laptop Computer A more efficient approach to creating dynamic WHERE clauses involves using the COALESCE function. This function returns the first non-null expression in its expression list. The following example shows how it works. DECLARE @Exp1 varchar(30), @Exp2 varchar(30) SET @Exp1 = NULL SET @Exp2 = 'SQL Server' SELECT COALESCE(@Exp1,@Exp2) --Results-- ------------------------------ SQL Server
laptop computers is driving strong sales for notebook computers, according to the latest quarterly sales figures from the research firm IDC, which reported a 37% computer sales for the second quarter, compared with a year earlier. In the U.S., laptop sales grew 17.7%, while sales of desktop computers and servers fell 4%. The New York Times ( 10), CNET ( 10)
Desktop Computer The function processes the expression list from left-to-right and returns the first non-null value. The COALESCE function can process an infinite number of expressions (e.g., COALESCE(@Exp1,@Exp2,@Exp3,@Exp4,...)), but for the example presented in this article only two are needed.
Notebooks Now that you know how COALESCE works, lets see how it is used to produce an efficient SELECT that supports a multi-parameter search. Each comparison operation in the WHERE clause must be modified to handle NULL values. The following shows the modified statement. SELECT Cus_Name, Cus_City, Cus_Country FROM Customers WHERE Cus_Name = COALESCE(@Cus_Name,Cus_Name) AND Cus_City = COALESCE(@Cus_City,Cus_City) AND Cus_Country = COALESCE(@Cus_Country,Cus_Country)
Lenovo The COALESCE function will return the first non-null value, so when a value is provided for a parameter it is used in the comparison operation. When a value is not supplied for a parameter, the current column value is used. A column value always equals itself, which causes all the rows to be returned for that operation.
Hard Drive Let's add some data to the Customers table with the following INSERTs so we can test the statement. INSERT Customers VALUES ('Acme','Paris','USA') INSERT Customers VALUES ('Bouvier','Paris','France') INSERT Customers VALUES ('Acme','Houston','USA')
Travelstar The following shows how to return all the rows with Cus_City value equal to 'Paris.' DECLARE @Cus_Name varchar(30), @Cus_City varchar(30), @Cus_Country varchar(30) SET @Cus_Name = NULL SET @Cus_City = 'Paris' SET @Cus_Country = NULL SELECT Cus_Name, Cus_City, Cus_Country FROM Customers WHERE Cus_Name = COALESCE(@Cus_Name,Cus_Name) AND Cus_City = COALESCE(@Cus_City,Cus_City) AND Cus_Country = COALESCE(@Cus_Country,Cus_Country) --Results-- Cus_Name Cus_City Cus_Country ------------------------------ --------------- --------------- Acme Paris USA Bouvier Paris France
Wrapping the Statement in a Stored Procedure
Gateway The most efficient way to implement this type of search is with a stored procedure. The statement shown here creates a procedure that accepts the required parameters. When a parameter value is not supplied it is set to NULL. CREATE PROCEDURE ps_Customers_SELECT_NameCityCountry @Cus_Name varchar(30) = NULL, @Cus_City varchar(30) = NULL, @Cus_Country varchar(30) =NULL AS SELECT Cus_Name, Cus_City, Cus_Country FROM Customers WHERE Cus_Name = COALESCE(@Cus_Name,Cus_Name) AND Cus_City = COALESCE(@Cus_City,Cus_City) AND Cus_Country = COALESCE(@Cus_Country,Cus_Country)
Laptop Parts The following shows that you can call the procedure with zero, one or more parameters to retrieve the desired resultset.
Software --Example 1 EXEC ps_Customers_SELECT_NameCityCountry --Results-- Cus_Name Cus_City Cus_Country ------------------------------ --------------- --------------- Acme Paris USA Bouvier Paris France Acme Houston USA
Hard Drives --Example 2 EXEC ps_Customers_SELECT_NameCityCountry @Cus_Country = 'USA' --Results-- Cus_Name Cus_City Cus_Country ------------------------------ --------------- --------------- Acme Paris USA Acme Houston USA
Electronics --Example 3 EXEC ps_Customers_SELECT_NameCityCountry @Cus_Name = 'Acme', @Cus_City = 'Houston' --Results-- Cus_Name Cus_City Cus_Country ------------------------------ --------------- --------------- Acme Houston USA
[ Comment, Edit or Article Submission ]
