Last post Nov 14, 2014 08:54 AM by eralper
Nov 13, 2014 07:40 AM|N1ZAM|LINK
I have a requirement were in I need to pass list of values to search against a string in Database. Say for instance I want to search skillset of a candidate in my db, how do i do it incase there are multiple values to be passed something like
I want the results were in I get all the candidates which have at least one of these skills NOT an Exact Match. Using IN will try to give an exact match so i am refraining from it. Any other approach that can be applied here?
Nov 13, 2014 07:54 AM|markfitzme|LINK
Sql Server has a few options depending upon the version. There are table value parameters, string splitting, and xml parameters. A great article illustrating the various types and how to go about them can be found at: http://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure
Nov 14, 2014 12:13 AM|N1ZAM|LINK
Here's the Stored Procedure...
ALTER PROCEDURE [dbo].[Search_All_Candidate]
@SkillSet VARCHAR (50) = NULL,
@CurrentLocation VARCHAR(25) = NULL
SELECT * FROM tbl_Candidate_Details C WHERE
(C.SkillSet LIKE '%'+@SkillSet+'%') AND
(@YearsOfExperience = 0 OR C.YearsOfExperience = @YearsOfExperience) AND
(C.Address LIKE '%'+@CurrentLocation+'%')
the exact @SkillSet value in their skillset rather i want all candidates with any one of these skillsets.
How do we go about it?
Nov 14, 2014 01:21 AM|raj.selvaa|LINK
You could try this
Create Table Test(Skills Varchar(50))
Insert into Test values('C')
Insert into Test values('C++')
Insert into Test values('C#')
Insert into Test values('Java')
Insert into Test values('PHP')
Insert into Test values('JQuery')
CREATE FUNCTION [dbo].[CSVToTable] (@InStr VARCHAR(MAX))
RETURNS @TempTab TABLE
(Skill varchar(100) not null)
;-- Ensure input ends with comma
SET @InStr = REPLACE(@InStr + ',', ',,', ',')
DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @INSTR ) <> 0
SELECT @SP = PATINDEX('%,%',@INSTR)
SELECT @VALUE = LEFT(@INSTR , @SP - 1)
SELECT @INSTR = STUFF(@INSTR, 1, @SP, '')
INSERT INTO @TempTab(Skill) VALUES (@VALUE)
Declare @Skills varchar(max)
--Select * FROM Test where Skills IN(@Skills)
SELECT * FROM Test WHERE Skills IN (SELECT * FROM dbo.CSVToTable(@Skills)
Nov 14, 2014 04:33 AM|sandeepmittal11|LINK
Create a split function in sql server and pass comma separated value as parameter to store procedure, then call split function with your parameter with
IN in your query. Some thing like this
select * from tablename where colname in dbo.split(@param)
Refer below post for split function
Nov 14, 2014 08:54 AM|eralper|LINK
Do you keep the skills of candidates as text or do you have a table for normalization?
Please check following select scripts
select * from dbo.split(@SkillSet,',')
--create table CandidateSkill2(CandidateId int, Skill nvarchar(100))
--insert into CandidateSkill2 select 1, 'C#'
--insert into CandidateSkill2 select 2, 'Tcl/Tk'
select distinct cs.CandidateId
from CandidateSkill2 cs
inner join dbo.split(@SkillSet,',') ss on ss.val = cs.skill
--create table CandidateSkill1(CandidateId int, SkillId int)
--create table Skill(SkillId int, Skill nvarchar(100))
--insert into Skill select 1, 'ASP.NET'
--insert into Skill select 2, 'C#'
--insert into CandidateSkill1 select 1, 1
--insert into CandidateSkill1 select 1, 2
--insert into CandidateSkill1 select 2, 9
select distinct cs.CandidateId
from CandidateSkill1 cs
inner join Skill s on s.SkillId = cs.SkillId
inner join dbo.split(@SkillSet,',') ss on ss.val = s.skill
You can use the following
SQL Split function for this task