Sql query , comparing a table field to a list of values , condition (MS SQL)
-
hi ,
i have a list of id values ( listOfProducts ) returned from a dropdown list , i need to use them to filter in a sql requestsomething like this :
SELECT stock_id , loc_id
FROM S_ware D
WHERE D.product_id IN (listOfProducts) (not sure about that)
i need to get all the records that have a product id that is contained inside the list from the dropdown
thanks
-
hi ,
i have a list of id values ( listOfProducts ) returned from a dropdown list , i need to use them to filter in a sql requestsomething like this :
SELECT stock_id , loc_id
FROM S_ware D
WHERE D.product_id IN (listOfProducts) (not sure about that)
i need to get all the records that have a product id that is contained inside the list from the dropdown
thanks
-
hi ,
i have a list of id values ( listOfProducts ) returned from a dropdown list , i need to use them to filter in a sql requestsomething like this :
SELECT stock_id , loc_id
FROM S_ware D
WHERE D.product_id IN (listOfProducts) (not sure about that)
i need to get all the records that have a product id that is contained inside the list from the dropdown
thanks
Send the list of products as comma seperated to parameter List of Products and change this query into Dynamic query.
For ex: @listOfProducts is parameter. Send as tilde or comma seperated to SQL query
Inside Query, split the comma seperated and change to list. Use the below script.
DECLARE @listOfProducts VARCHAR(2000)
SET @listOfProducts='174~175~78~77~191~399~451'
IF OBJECT_ID('tempdb..#listOfProducts') IS NOT NULL DROP TABLE #listOfProductsSELECT
DISTINCT Split.a.value('.', 'VARCHAR(100)') AS Prod INTO #listOfProducts
FROM (SELECT
CAST ('' + REPLACE(@listOfProducts, '~', '') + '' AS XML) AS String )
AS A CROSS APPLY String.nodes ('/M') AS Split(a)
WHERE Split.a.value('.', 'VARCHAR(100)')<>''SELECT stock_id , loc_id
FROM S_ware D
WHERE D.product_id IN (select Prod from #listOfProducts) -
Send the list of products as comma seperated to parameter List of Products and change this query into Dynamic query.
For ex: @listOfProducts is parameter. Send as tilde or comma seperated to SQL query
Inside Query, split the comma seperated and change to list. Use the below script.
DECLARE @listOfProducts VARCHAR(2000)
SET @listOfProducts='174~175~78~77~191~399~451'
IF OBJECT_ID('tempdb..#listOfProducts') IS NOT NULL DROP TABLE #listOfProductsSELECT
DISTINCT Split.a.value('.', 'VARCHAR(100)') AS Prod INTO #listOfProducts
FROM (SELECT
CAST ('' + REPLACE(@listOfProducts, '~', '') + '' AS XML) AS String )
AS A CROSS APPLY String.nodes ('/M') AS Split(a)
WHERE Split.a.value('.', 'VARCHAR(100)')<>''SELECT stock_id , loc_id
FROM S_ware D
WHERE D.product_id IN (select Prod from #listOfProducts) -
hi ,
i have a list of id values ( listOfProducts ) returned from a dropdown list , i need to use them to filter in a sql requestsomething like this :
SELECT stock_id , loc_id
FROM S_ware D
WHERE D.product_id IN (listOfProducts) (not sure about that)
i need to get all the records that have a product id that is contained inside the list from the dropdown
thanks
There's no table named "S_ware" ... so the FROM clause will issue (most likely) "Invalid object name". I've also noticed that, in the second post to this thread, you've given a bit more information ... but you're declaring a variable as VARCHAR; the SET is ok. But suddenly 'listOfProducts' has become a temporary TABLE. So there are naming issues also. This all sounds like pedantry from me so I'll quit. Suffice to say I'd look up TABLE, SET, DECLARE, and the advanced XML in the BOL for SQL Server version you think you have.
