Ryan writes
"I am looking for a way of creating a stored procedure that will carry out a keyword search without building up the SQL dynamically in the stored procedure. What I'm after is a method of doing this that doesn't require me to build up a huge string." "When I have to match a list of IDs I would typically parse the comma delimted list of IDs and insert them in to a temporary table and then use an IN clause as follows: SELECT @strEmployeeID = @strEmployeeID + ',' CREATE TABLE #employees (EmployeeID int not null) IF CHARINDEX(',', @strEmployeeID) > 0 BEGIN WHILE CHARINDEX(',',@strEmployeeID) > 0 BEGIN INSERT #employees VALUES(SUBSTRING(@strEmployeeID, 1, CHARINDEX(',', @strEmployeeID) - 1)) SELECT @strEmployeeID = STUFF(@strEmployeeID, 1, CHARINDEX(',', @strEmployeeID),'') END END SELECT * FROM Job WHERE EmployeeID IN (SELECT EmployeeID FROM #employees) Can anyone offer me a similar solution where I parse the keywords in to a temporary table of some sort or is my only option to build a monster string ?"
Laptop Battery Sure Ryan. There are a few ways to do this. One way is used on SQL Team, Graz wrote a great article about it here . A few months ago, I was building a similar site to this one and had an idea of a different way to go about it.
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 First some background. Rob Volk wrote an article at the beginning of this year on Parsing CSV Values Into Multiple Rows using a "tally" or "sequence" table method. If you are not familiar with this article and/or technique I strongly suggest you go read that now. If it is new to you, read it a few times, it may take a bit to get your head around it. Essentially, it splits out a comma delimited list and returns a set.
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 technique I am going to demonstrate takes this approach.
You searched for information on battery and laptop computer. Your new compaq laptop battery comes in a discharged condition and must be charged before use (refer to your computer manual for charging instructions). Of course, once you decide on the laptop computer battery you need, you will want to receive it right away. Plug battery pack into DC input of your laptop computer which is located in backside of laptop usually. Toshiba laptop battery laptop batteries, laptop battery, toshiba laptop battery, batteries for laptop computer.
Laptop Computers Firstly, we need our tally table. I am going to call my table "Sequence".
Create Table Sequence( Seq int ) --Generate some data for it SET NOCOUNT ON Declare @i int Set @i = 1 WHILE @i
Cookies are also used by some of the search engines such as Overture to help us determine which "keywords" are searched most frequently by our customers making purchases. This information remains anonymous and is a tally only of specific keywords.
Laptop Computer Now take our list of keywords (in this case, they are space delimited) and convert them into a Set. Paste this code into Query Analyzer and have a look.
Declare @Keywords varchar(2000) Select @Keywords = 'my space delimited string' Select Substring(' ' + @keywords + ' ',seq, CharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq) FROM SEQUENCE WHERE seq 0
- Use this tool to determine the best keywords and pages of your website to submit to search engines.
- Server Uptime Monitor
Desktop Computer Pretty cool huh ?
Notebooks Next up, we JOIN the article table to this set based on the body text. Where a string in the article body matches one of the words in this set, the join condition will match and the table will join. Where the word appears more than once, the table will join multiple times. If you group these and and count the number of joins ... voila! ... instant ranked search results.
Lenovo Here is some code...
--Assuming we have an Articles table Create Table Articles( ArticleID int NOT NULL, ArticleTitle VarChar(200) NOT NULL, ArticleDescription VarChar(500) NOT NULL, ArticleBody Text ) Declare @Keywords varchar(2000) Select @Keywords = 'my space delimited string' Select ArticleID, ArticleTitle, ArticleDescription, count(AA_ID) hits FROM SEQUENCE INNER JOIN Articles ON Articles.ArticleBody like '%' + Substring(' ' + @keywords + ' ',seq, CharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq) + '%' WHERE seq 0 Group by ArticleID, ArticleTitle, ArticleDescription ORDER BY Hits DESC
Hard Drive So there you have it, a simple way to use the "tally" table to produce ranked keyword searches.
Travelstar The "tally" table technique (say that 6 times fast) is a very powerful tool and a good one to have in your arsenal whenever you need to split delimited strings of words or numbers.
Gateway Good luck...and happy coding.
[ Comment, Edit or Article Submission ]
