August 23, 2006 at 4:23 am
use Northwind
declare @check varchar(50)
set @check = '''FISSA'',''FOLIG'''
print @check
select * from Customers where CustomerID in (@Check)
I am forming the above String and Placing in the variable @check.
The String is passed as 'FISSA','FOLIG', but no result is returned from the select query. I am unable to use the @check variable in the IN Clause Type Query.
Find me a solution.
ASHOK S
August 23, 2006 at 4:30 am
You can't do that this way - a variable can not hold several comma-delimited values. There are two ways to solve such requirement - either use dynamic SQL (not really recommended here), or parse the string into a table (temporary table or table variable) and then join to this instead of using IN.
You can find lots of info about similar solutions and their problems, and things to avoid, on Erland Sommarskog's pages - there are several brilliant articles referring to that (start with "Arrays and Lists in SQL Server").
August 23, 2006 at 7:07 am
You could do it this way
WHERE @Check LIKE '''%' + CustomerID + '%'''
Far away is close at hand in the images of elsewhere.
Anon.
August 23, 2006 at 10:31 pm
Both are working.
but i am unable to understand
WHERE @Check LIKE '''%' + CustomerID + '%'''
this query. what's process behind this.
August 24, 2006 at 12:44 am
Well, I suppose you understand how standard condition
WHERE my_column LIKE '%new%'
works. It returns all rows where the string in my_column contains "new". The query you are asking about does precisely the same, but since @check is a list of values, you have to approach it from the other side. Instead of looking, whether the supplied string is contained in the value of a column (it probably never is, unless the list has only 1 value), you simply look whether the column value is contained in the supplied string.
It works, but be careful - if the list is long and the number of searched rows high, performance will not be good. In case you need better performance, study the article mentioned in my first reply. You'll find more effective ways of doing it there.
August 25, 2006 at 2:28 pm
If you are using SQL 2K or greater, you could great a user defined functions that returns a table variable.
Pass the comma delimited array to the UDF which parses it into the table variable which is passed back. This is then used in the from clause as such:
select a.* from Customers a inner join UDF ON a.CustomerID like UDF.CustId
Dave N
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply