"m trying to compare a variable (@item) to a field which has multiple item numbers separated by commas. I've used "LIKE" and "IN" to no avail. I've also tried appending wildcard characters to the variable (@item2), which also hasn't worked. Full-text indexing is not activated on the DB (SQL Server 2000), so I can't use "CONTAINS". So, in the code below, I"m trying to compare @item which may look like "0282" to ItemString which may look like "0004,0089,0282". ItemString is varchar(255). If ItemString only has one item number in it, it will work using "IN". Any takers? CREATE PROCEDURE dbo.pGetOrderLotData @order char(8), @item char(5), @package char(20) output, @sodship char(30) output, @soistring varchar(60) output, @soqstring varchar(60) output, @item2 varchar(7) output AS set nocount on -- DECLARE @item2 varchar(6) SET @item2 = '%' + @item + '%' Drop Table tCLsearchorder1 Create Table tCLsearchorder1 (sopackage char(8), soorder char(8), sodateship datetime, soitemstring varchar(60), soqtystring varchar(60)) Select @order = OrderNumber, @package = PackageNumber, @sodship = DateShipped, @soistring = ItemString, @soqstring = QuantityString From tblPackage Where OrderNumber = @order AND @item2 LIKE ItemString -- may have to use shipdate instead of @item --SELECT DATEPART(mm, @sodship) + "/" + DATEPART(dd, @sodship) + "/" + DATEPART(yyyy, @sodship) AS '@sodship' IF @package IS NULL Begin Set @package = 'Invalid Order#!' GOTO nomatch End Else Insert INTO tCLsearchorder1 (sopackage, soorder, sodateship, soitemstring, soqtystring) VALUES (@package, @order, @sodship, @soistring, @soqstring) nomatch: GO
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!