This syntax Role in (' + @role + ')' is not going to work. You need to split the list into individual tables using fn_split or other split functions we already discussed.
The syntax is going to be
select ... inner join dbo.fn_split(@Role) RolesList on myTable.Role = RolesList.Col1
As you mentioned your data has comma at the end, while comparing, both the values should be alike (even with the space also). Then only comparisons work. Here we are trying to compare a commaless value with the comma plus value. So, the solution is both
should have comma or both should not have comma.
Try this, where the comparison is done after removing the comma from the database value. As I said previous, if there is any space between the value and comma then also the comparison may not work. In that case, try removing the leading and traling spaces
using the ltrim and rtrim functions.
SET @SQL='SELECT Companyname, Role, Moreaboutjob, candidate,Experience,Location,salarymin,salarymax,Interviewdate,postdate
FROM callcenter where Location Like ''%' + @city
+ '%'' and DATEDIFF(Day, postdate, getdate( ) ) <='
+ CAST(@datediff
AS VARCHAR(3))
+ ' and
REPLACE(Role,'','','''') in (' + @role
+ ')'
Vijaya Krishna Paruchuri
Please remember to click “Mark as Answer” on the post that helped you. This credits that member, earns you a point and marks your thread as resolved.
Marked as answer by closeguy2005 on Sep 19, 2008 07:52 PM
As you mentioned your data has comma at the end, while comparing, both the values should be alike (even with the space also). Then only comparisons work. Here we are trying to compare a commaless value with the comma plus value. So, the solution is both
should have comma or both should not have comma.
Try this, where the comparison is done after removing the comma from the database value. As I said previous, if there is any space between the value and comma then also the comparison may not work. In that case, try removing the leading and traling spaces
using the ltrim and rtrim functions.
Thank you Vijaykrishna..you are undoubtably brilliant..
Finding solution such as CAST(@datediff
AS VARCHAR(3))
+ ' and
REPLACE(Role,'','','''') in (' + @role
+ ')' is
not easy task....your concepts are soo strong i must say
I can see jobs of all roles selected by user..
Thanks a lot again..& thanks to naomi to for his sincere efforts to help..
Marked your answer as ANSWER
If possible please let me know why have you used SET @role = '''' + REPLACE(@role,',',''',''') + ''''
I am not getting why have you used so many single quotes..
If possible please let me know why have you used SET @role = '''' + REPLACE(@role,',',''',''') + ''''
Here, it is in a string that is started with a single quote and ended with a single quote. In SQL, in string, we put two single quotes to represent a single 'single quote'. That is it.
I suggest you that to remove the comma from your Role field, if it does not serve any other purpose. Even if we achieved the result, the REPLACE() function obivously puts overhead on SQL Server.
Vijaya Krishna Paruchuri
Please remember to click “Mark as Answer” on the post that helped you. This credits that member, earns you a point and marks your thread as resolved.
If I can have a say on this issue, I believe we approached it wrongly. In this particular case there was no need for dynamic query and even if we make it to work, it's much better not to use it. The simplest suggestion of just splitting the roles into separate
table should have worked for you.
I would suggest to you to try to experiment with alternative solution too.
Beware of bugs in the above code; I have only proved it correct, not tried it. (Donald Knuth)
If I can have a say on this issue, I believe we approached it wrongly. In this particular case there was no need for dynamic query and even if we make it to work, it's much better not to use it. The simplest suggestion of just splitting the roles into separate
table should have worked for you.
I would suggest to you to try to experiment with alternative solution too.
Here, I would like to bring to your notice that, we are creating a single statement only dynamically and executing it only once. Here, I believe that the resources consumed by the creation of this dynamic sql is more cheaper than an outside function call.
However, I am not sure which one is better whether the IN clause or Inner Join. Does SQL Server creates execution plan alike? So, it is better to give a try on both the ways on somewhat large sized data and go with the better performer.
Vijaya Krishna Paruchuri
Please remember to click “Mark as Answer” on the post that helped you. This credits that member, earns you a point and marks your thread as resolved.
I suggest you that to remove the comma from your Role field, if it does not serve any other purpose.
Its needed because while employer submits jobs he may choose multiple roles suitable for that position and that will
be saved in role field. To seperate those positions i used comma there , so that user can differentiate between those
positions.
Example : Fresher ,Customer service executive (Non voice) ,
I noticed just now, when i store multiple positions then I am not able to search them.(Even when i select single job for searching). I think our working query needs little bit modification...
(what I want is , even if user selects Fresher or Customer service executive or both, this job entry should get displayed)
Naomi, since we have already spent so much time on this, i find no reason to choose different way..If at all it becomes hindrance for the performance then we can choose the other way..thanks anyways..
Your database structure make it difficult using IN clause. Now we should listen to Naom [:)]. Try the following. As we used LIKE in join, the query may return duplicate records. To suppress these duplicate returns we should have a distinct column. For this
purpose I suggestively used the column name callcenterID, but it would have different name in your table, please use that name. Note: You have to have the function FN_SPLIT that I posted earlier.
ALTER PROCEDURE dbo.bporesults
(
@datediff int ,
@city varchar(20),
@role varchar(100)
)
AS
SELECT DISTINCT callcenterID, Companyname, Role, Moreaboutjob, candidate,Experience,
Location,salarymin,salarymax,Interviewdate,postdate
FROM callcenter INNER JOIN dbo.fn_split(@role) r
ON callcenter.Role LIKE ('%' + r.SplitItem + '%')
and Role Like @role
where Location Like '%' + @city + '%'
and DATEDIFF(Day, postdate, getdate( ) ) <= @datediff
Vijaya Krishna Paruchuri
Please remember to click “Mark as Answer” on the post that helped you. This credits that member, earns you a point and marks your thread as resolved.
closeguy2005
Participant
887 Points
1746 Posts
Re: problem in pattern matching..
Sep 18, 2008 07:50 PM|LINK
See..In role field I store data like this
Technical support executive(Non voice) ,
1.Each role field holds just one role. Also, please check comma at the end.
2.Hence each row holds one role
3. One more example
Fresher ,
IMPORTANT :
Hi, this info my be important. To search jobs, I have listbox which contains multiple roles. User can select any number
of roles out of them.
To check , which roles user has selected I do following
dim moreabout as String
moreabout=Nothing
For i = 0 To listrole.Items.Count - 1
If (listrole.Items(i).Selected) Then
moreabout = moreabout & listrole.Items(i).Text & " ,"
End If
Next
Something wrong here? I used break point & tested that variable moreabout holds :
Fresher ,Vp-operations ,
Hence @Role variable in stored procedure holds Fresher ,Vp-operations ,
closeguy2005
Participant
887 Points
1746 Posts
Re: problem in pattern matching..
Sep 18, 2008 07:53 PM|LINK
Please check my post above
Do we need to add % ( pattern matching) for ROLE in sql query ?
Are we missing it?
Also, do we need to use LIKE operator insted of IN here Role in (' + @role + ')' ?
Please give modified syntax..
Naom
All-Star
36004 Points
7901 Posts
Re: problem in pattern matching..
Sep 19, 2008 03:48 PM|LINK
This syntax Role in (' + @role + ')' is not going to work. You need to split the list into individual tables using fn_split or other split functions we already discussed.
The syntax is going to be
select ... inner join dbo.fn_split(@Role) RolesList on myTable.Role = RolesList.Col1
Or if you're using the ufn_alines I gave a link before http://berezniker.com/content/pages/sql/microsoft-sql-server/alines-udf-string-split it would be
select .. from myTable inner join dbo.ufn_alines(@Role,',') RolesList on myTable.Role = RolesList.Item
(Donald Knuth)
Visit my blog
Microsoft Community Contributor 2011-12
vijayakrishn...
Participant
1288 Points
225 Posts
Re: problem in pattern matching..
Sep 19, 2008 04:16 PM|LINK
As you mentioned your data has comma at the end, while comparing, both the values should be alike (even with the space also). Then only comparisons work. Here we are trying to compare a commaless value with the comma plus value. So, the solution is both should have comma or both should not have comma.
Try this, where the comparison is done after removing the comma from the database value. As I said previous, if there is any space between the value and comma then also the comparison may not work. In that case, try removing the leading and traling spaces using the ltrim and rtrim functions.
SET @SQL='SELECT Companyname, Role, Moreaboutjob, candidate,Experience,Location,salarymin,salarymax,Interviewdate,postdate FROM callcenter where Location Like ''%' + @city + '%'' and DATEDIFF(Day, postdate, getdate( ) ) <=' + CAST(@datediff AS VARCHAR(3)) + ' and REPLACE(Role,'','','''') in (' + @role + ')'Please remember to click “Mark as Answer” on the post that helped you. This credits that member, earns you a point and marks your thread as resolved.
closeguy2005
Participant
887 Points
1746 Posts
Re: problem in pattern matching..
Sep 19, 2008 07:50 PM|LINK
Thank you Vijaykrishna..you are undoubtably brilliant..
Finding solution such as CAST(@datediff AS VARCHAR(3)) + ' and REPLACE(Role,'','','''') in (' + @role + ')' is
not easy task....your concepts are soo strong i must say
I can see jobs of all roles selected by user..
Thanks a lot again..& thanks to naomi to for his sincere efforts to help..
Marked your answer as ANSWER
If possible please let me know why have you used SET @role = '''' + REPLACE(@role,',',''',''') + ''''
I am not getting why have you used so many single quotes..
vijayakrishn...
Participant
1288 Points
225 Posts
Re: problem in pattern matching..
Sep 19, 2008 08:20 PM|LINK
Here, it is in a string that is started with a single quote and ended with a single quote. In SQL, in string, we put two single quotes to represent a single 'single quote'. That is it.
I suggest you that to remove the comma from your Role field, if it does not serve any other purpose. Even if we achieved the result, the REPLACE() function obivously puts overhead on SQL Server.
Please remember to click “Mark as Answer” on the post that helped you. This credits that member, earns you a point and marks your thread as resolved.
Naom
All-Star
36004 Points
7901 Posts
Re: problem in pattern matching..
Sep 19, 2008 08:24 PM|LINK
If I can have a say on this issue, I believe we approached it wrongly. In this particular case there was no need for dynamic query and even if we make it to work, it's much better not to use it. The simplest suggestion of just splitting the roles into separate table should have worked for you.
I would suggest to you to try to experiment with alternative solution too.
(Donald Knuth)
Visit my blog
Microsoft Community Contributor 2011-12
vijayakrishn...
Participant
1288 Points
225 Posts
Re: problem in pattern matching..
Sep 19, 2008 08:45 PM|LINK
Here, I would like to bring to your notice that, we are creating a single statement only dynamically and executing it only once. Here, I believe that the resources consumed by the creation of this dynamic sql is more cheaper than an outside function call.
However, I am not sure which one is better whether the IN clause or Inner Join. Does SQL Server creates execution plan alike? So, it is better to give a try on both the ways on somewhat large sized data and go with the better performer.
Please remember to click “Mark as Answer” on the post that helped you. This credits that member, earns you a point and marks your thread as resolved.
closeguy2005
Participant
887 Points
1746 Posts
Re: problem in pattern matching..
Sep 20, 2008 09:55 AM|LINK
Its needed because while employer submits jobs he may choose multiple roles suitable for that position and that will
be saved in role field. To seperate those positions i used comma there , so that user can differentiate between those
positions.
Example : Fresher ,Customer service executive (Non voice) ,
I noticed just now, when i store multiple positions then I am not able to search them.(Even when i select single job for searching). I think our working query needs little bit modification...
(what I want is , even if user selects Fresher or Customer service executive or both, this job entry should get displayed)
Naomi, since we have already spent so much time on this, i find no reason to choose different way..If at all it becomes hindrance for the performance then we can choose the other way..thanks anyways..
vijayakrishn...
Participant
1288 Points
225 Posts
Re: problem in pattern matching..
Sep 20, 2008 04:46 PM|LINK
Your database structure make it difficult using IN clause. Now we should listen to Naom [:)]. Try the following. As we used LIKE in join, the query may return duplicate records. To suppress these duplicate returns we should have a distinct column. For this purpose I suggestively used the column name callcenterID, but it would have different name in your table, please use that name. Note: You have to have the function FN_SPLIT that I posted earlier.
ALTER PROCEDURE dbo.bporesults ( @datediff int , @city varchar(20), @role varchar(100) ) AS SELECT DISTINCT callcenterID, Companyname, Role, Moreaboutjob, candidate,Experience, Location,salarymin,salarymax,Interviewdate,postdate FROM callcenter INNER JOIN dbo.fn_split(@role) r ON callcenter.Role LIKE ('%' + r.SplitItem + '%') and Role Like @role where Location Like '%' + @city + '%' and DATEDIFF(Day, postdate, getdate( ) ) <= @datediffPlease remember to click “Mark as Answer” on the post that helped you. This credits that member, earns you a point and marks your thread as resolved.