Consider we have two tables 'tbl_dealer' and 'tbl_item' for dealer and items respectively
'item_id'(of type int) is the primary key of 'tbl_item' and 'dealer_id' is the primary key of 'tbl_dealer'.
dealer table have a column 'dealer_items' of type varchar. This column stores the comma seperated item_id s for
a particular dealer
Suppose we have to find the all items for a particular dealer with dealer_id=1
Then most of you will use the query:
select item_id,item_name from tbl_item where item_id IN (SELECT dealer_item from tbl_dealer where dealer_id=1)
It will results the following error in sql server:'Syntax error converting the varchar value '1,2,3' to a column of data type int'
Here '1,2,3' is the result of the subquery 'SELECT dealer_item from tbl_dealer where dealer_id=1'
Why this happen? because we are composing an SQL statement dynamically, and '1,2,3' is just one value in the IN clause.
We can solve this by extracting the integer(or any other type) values from the varchar value.
We can do this by using the builting functions:PATINDEX or CHARINDEX, or using the LIKE clause.
The code for each are shown below:
using LIKE
----------
DECLARE @itemids VARCHAR(50)
set @itemids =(SELECT dealer_item from tbl_dealer where dealer_id=1 )
SELECT item_id,item_name
FROM tbl_item
WHERE ',' + @itemids + ',' LIKE '%,' + cast(item_id as varchar(50)) + ',%';
using CHARINDEX
---------------
DECLARE @itemids VARCHAR(50)
SET @itemids =(SELECT dealer_item from tbl_dealer where dealer_id=1)
SELECT item_id,item_name
FROM tbl_item
WHERE CHARINDEX( ',' + cast(item_id as varchar(50)) + ',', ',' + @itemids + ',' ) > 0 ;
using PATINDEX
--------------
DECLARE @itemids VARCHAR(50)
set @itemids =(SELECT dealer_item from tbl_dealer where dealer_id=1 )
SELECT item_id,item_name
FROM tbl_item
WHERE PATINDEX( '%,' + cast(item_id as varchar(50)) + ',%', ',' + @itemids + ',' ) > 0;
Note:-All the above three methods, i converted the integer 'item_id' to varchar.It is compulsory
If the item_id is already varchar, there is no need to convert it.
If you like to study the advanced methods for solvin this, it is better to visit the following link:
Arrays and Lists in SQL Server
Happy Programming
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment