Multple value hold a single variable in Sql

Question

0.00/5 (No votes)

See more:

Hi
I working on one project. This project like a job Seeker. I think every Guy's familiar all ready Similar Type Job.I want This type of Work use in my Project. i have done all this but a bit Problem is stop my working.Suppose I run this Procedure like this type

SQL

exec [sp_job_SimilarJob] 'sql'

My Job Opening Table like this
SrNo Skill Required
1 Sql,Java,mvc
2 Sql,c#
3 java,php,Sql and So many type of data this type.

when i exec this procedure bulk of data return like this

SQL

select @my=o.SrNo from tbl_Job_OrgOpeningDetail o where SkillsRequired like '%' + Sql+'%'

The above query return both of Row like this

SQL

SrNo.
1
2
3

my Complete Store Procedure show below

SQL

ALTER proc [dbo].[sp_job_SimilarJob]
(
@products nvarchar(50)=null
)
as 
create table #mytb(dd varchar(max))
Declare @individual varchar(20) = null
declare @my varchar(max)=null
declare @testing int=0
WHILE LEN(@products) >= 0
BEGIN
    IF PATINDEX('%,%',@products) > 0
    BEGIN

        SET @individual = SUBSTRING(@products, 0, PATINDEX('%,%',@products))
        
select @my=o.OrgJob_id from tbl_Job_OrgOpeningDetail o where SkillsRequired like '%' + @individual  +'%'

---how can hold this multiple values in @my after the hold values insert in #mytb table

        SET @products = SUBSTRING(@products, LEN(@individual + ',') + 1, LEN(@products))
        insert into #mytb values(@my)
       
    
    END
    ELSE
    BEGIN
        SET @individual = @products
        SET @products = NULL
     insert into #mytb values(@my)
    END
    END 
select org.SkillsRequired, org.JobCatagary ,cty.City,fa.FuncationalArea,orgdt.OrganisationName,jq.Qualification,
org.OrgDt_id,org.MinimumExprinceYear,org.JobDescription ,org.OrgJob_id,
(select top 1 VoteCount from Tbl_Job_Vote_UP_Down where OrgJob_ID=org.OrgJob_id order by VoteCount Desc )as VoteCount
  
from tbl_Job_OrgOpeningDetail org 

where org.OrgJob_id in (select dd from #mytb)

return
exec [sp_job_SimilarJob] 'sql,c++,java'</blockquote>

my question is how can hold multiple values in temp table. For any query hit to comment i am ready for reply.

Thanks Advance
Happy Coding ☺
jsb

Posted 25-Dec-13 8:23am

joginder-banger

Updated 25-Dec-13 8:24am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Answer 1 · 2013-12-25T08:46:00

OK, now do two things for me. First, say out loud what you are trying to do. 'Find jobs that match a specific skill'. That's a simple, core thing your system has to do. It will be done a lot. Now, look at your code. Is it simple ? No, it's a complex disaster. Your design is broken. It's broken because you're using one cell in the DB to store a combination of things, instead of a single value. You should always avoid using temp tables. You should NEVER be splitting values up in SQL, if you can avoid it. A value in the DB should be one value, and you should avoid things like LIKE, because once you use them, all the indexes that make a DB fast, cannot be used.

So, what's the answer ? Fix your database. Everything you've done, needs throwing away. Start with this:

Create table Skill
(
SkillId int not null Identity(1,1).
Skill varchar(50)
)

Create Table JobOpening
(
JobId int not null identity (1,1),
.... ( other data you want for a job )
)

Create Table JobSkills
(
JobId int,
SkillId int
)

This is called a joining table, it lets you create relationships between two tables. So, taking your data example above:

SrNo Skill Required
1 Sql,Java,mvc
2 Sql,c#
3 java,php,Sql and So many type of data this type.

This would three jobs, with ids of 1,2 and 3. The Skills Table might have rows like this:

1, Sql
2, Java
3, MVC
4, C#
5, PHP

and then your JobSkills table would have entries like this

1, 1
1, 2
1, 3
2, 1
2, 4
3, 2
3, 5
3, 1

Where the first column is the job id and the second is the id of a skill.

Then your SQL is easy

select * from jobs j
inner join jobskills js on js.jobid = j.jobid
inner join skills s on s.skillid = js.skillid
where s.skill = @skill

if you had more than one skill to search for, it becomes a little more complex, if you want to match them all, but if you only want to match one, you pass your list in as XML and then use SQL to turn the XML in to a list and do an inner join on that list. Then you don't need a where statement at all. You only need one index in this DB, on the skill name, for this to work well. Of course, you'd define foreign keys between the three tables as well.