Hi, I am new to sql and testing a small project so I can understand how the paramater using "like" in the WHERE clause works.
Here are the values from @NewID parameter: B001NEW,C002NEW,C003NEW,C004NEW,C005NEW,C006NEW,C007NEW, and so on.
@NewID is pulling from a field called "InventoryPart" and the data type is nvarchar(7).
"InventoryPart" has 768 total distinct records.
Here is my SELECT statement:
DECLARE @NewID NVARCHAR(7)
SET @NewID = '035'
SELECT WarehouseID as Warehouse, WarehousePhone as Phone, SUBSTRING(InventoryPart, 2, 4) as Part, Price
FROM CARINVENTORYPARTS
WHERE SUBSTRING(InventoryPart,2,4) like (@NewID)
ORDER BY SUBSTRING(InventoryPart, 2, 4)
Currently, this query wont execute because of the SUBSTRING(InventoryPart,2,4) like (@NewID) in my where clause.
So, here are my questions:
How do update above query so it will run with single value parameter and multiple parameter and all parameter?
You can either use Dynamic SQL, which I don't recommend here if the parameter is "public facing" and you do nothing to check for SQL Injection, or you can split the parameter using an iTVF (Inline Table Valued Function... much more performant than mTVFs, multi-statement Table Valued Functions), or you can use Table Valued Parameters, or you could use an SQL CLR splitter.
Before I can make a recommendation, what is the maximum byte length of @Parameter that you would expect?
'_' will match any character
'%' will match zero or more [any] characters
So you could do
WHERE InventoryPart LIKE '_' + @NewID + '%'
However, it is pretty inefficient
Better would be to have additional columns for the various "parts" of the InventoryPart so taht you can query them individually, and they can be indexed etc.
This is the difference between the "code/name" that Humans want to give to an Inventory Item ... and what best suits computers to find / manage them
Ah... my apologies. I might be confusing the issue based on the above. Are you passing just one value in @NewID like you did in the SET statement above or are you passing multiple values as a comma separated string like you have in your "and so on" comment?
Sorry for the confusion and no need to apologies. Passing one value in my current query is working fine but I just want to make it work with multiple values in my @NewID parameters and also "ALL" values as well.
Below select statement would be a good example if I want to run the query with multiple values (random values from :InventoryPart)
DECLARE @NewID NVARCHAR(7)
SET @NewID = '027,035,041,072,055' --random multiple values
SELECT WarehouseID as Warehouse, WarehousePhone as Phone, SUBSTRING(InventoryPart, 2, 4) as Part, Price
FROM CARINVENTORYPARTS
WHERE SUBSTRING(InventoryPart,2,4) like (@NewID)
ORDER BY SUBSTRING(InventoryPart, 2, 4)
Given that your list of matches might be large (you say perhaps 8K) then the "cheap" workaround for this using CHARINDEX or REPLACE would be very inefficient.
You need to take the @ParameterList (which is, say, comma delimited) and SPLIT it into a temporary table - so, in effect, you will then have one parameter per ROW in the #TempTable) and then you will be able to do:
SELECT WarehouseID as Warehouse, WarehousePhone as Phone, SUBSTRING(InventoryPart, 2, 4) as Part, Price
FROM CARINVENTORYPARTS
JOIN #TempTable AS T
ON InventoryPart LIKE '_' + T.MatchString + '%'
ORDER BY SUBSTRING(InventoryPart, 2, 4)
BUT ... ... IF you had that part of the InventoryPart in its own column you would be able to do:
JOIN #TempTable AS T
ON InventoryPartNumberElement = T.MatchString
and IF you had an index on InventoryPartNumberElement then the performance would be superb. Without it, and say 8K of match elements (i.e. about 2000 elements) and assuming that the CARINVENTORYPARTS parts table has many rows (10,000 or 100,000) then performance will be terrible ...
What is absolutely the fastest method is to scan the value of @NewID for any SQL Injection attempts and then convert your query to dynamic SQL. The second fastest would be to split your parameter into a table valued variable and join against that. If you decide to use the latter route, post back and I'll give you a link for a pretty good splitter.
... IF you had that part of the InventoryPart in its own column you would be able to do: