February 9, 2012 at 4:50 pm
(SELECT Item as industry FROM AllJobCategories j CROSS APPLY dbo.DelimitedSplit8k(jobcategories1,'(') split
where j.UniqueId = 'DF036598-E190-422A-863B-000036B64FFE' and itemnumber = 1)
(select top 1 i.id from industries i where i.name = 'industry')
I cant get the 'industry' value to be passed from one query to another.
I tried a local variable, and that also failed.
Thanks
February 9, 2012 at 5:48 pm
This appears to be a part of a much bigger process. Could you elaborate what all you are doing and maybe we can help simplify that process.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 10, 2012 at 5:58 am
This is the error:
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value '@Industryid' to data type int.
It works fine on @Industryid = 0, but not when it has any other number.
USE [JobPortalIAN]
GO
/****** Object: StoredProcedure [dbo].[GetAllResumesSearchedDynamicQuery] Script Date: 02/10/2012 09:41:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,Ian>
-- Create date: <Create Date,21-Jan-2009,>
-- Description: <Description,[GetResumesSearchedListFullTextSearch],>
-- 1/14/11 - ian changes
--
-- =============================================
ALTER PROCEDURE [dbo].[GetAllResumesSearchedDynamicQuery]
-- Add the parameters for the stored procedure here
@Name varchar(250),
@Name1 varchar(250),
@Date int,
@Stateid int,
@CityId int,
@Industryid int,
@Degree int,
@Skillsid int,
@FutureSkillsid int,
@ExpectedSalary int,
@ExpectedMaxSalary int,
@Position varchar(100),
@Phrase int,
@fromRec int,
@toRec int
AS
declare @Query as varchar(8000)
--DECLARE @sunday datetime
--SET @sunday = (select dateadd(d,-1*datediff(d,'20080106',getdate())%7,getdate()))
--select @sunday
set @Query = 'Begin
WITH Res AS
(
select
p.id as Id,
p.regionid,
p.cityid,
p.countryid,
TotalYrsExp as experience,
(SELECT Item FROM AllJobCategories j CROSS APPLY dbo.DelimitedSplit8k(jobcategories1,''('') split
where j.UniqueId = p.id and itemnumber = 1) as Industry,
--(select top 1 i.id from industries i inner join workexperiences w on i.id = w.industryId where p.id = w.profileid) as IndustryId,
(select top 1 i.name from industries i where i.id = ''@Industryid'') as Industryianname,
--p.IndustryID,
p.AcademicExperienceTypeID,
--w.position,
degree3id, degree2id, degree1id,
case
when degree3id > degree2id then isnull((select top 1 d.Degree3 from AllDegrees d where p.id=d.uniqueid),'''')
when degree3id < degree2id then isnull((select top 1 d.Degree2 from AllDegrees d where p.id=d.uniqueid),'''')
when degree2id > degree1id then isnull((select top 1 d.Degree2 from AllDegrees d where p.id=d.uniqueid),'''')
else isnull((select top 1 d.Degree1 from AllDegrees d where p.id=d.uniqueid),'''')
end
as degree,
isnull((select wa.[RecentWage] from AllRecentWages wa where wa.UniqueId=p.id),'''') as Salary,
(select a.RecentJobTitle from AllRecentJobTitles a where a.Uniqueid = p.id) as position,
--case when len(w.position) < 30 then w.position
--else
--substring(w.position,1,30) + ''...'' end
--as Position,
p.InputDate,
p.SalaryMinID,
p.SalaryMaxID,
p.HomePhone,
p.CellPhone,
p.WorkPhone,
p.PrimaryEmailAddress,
p.SecondaryEmailAddress,
--isnull((select [Name] + '' '' from salutationtypes st
-- where p.SalutationId=st.id),'''') +
isnull(p.FirstName,'''') + '' '' + isnull(p.MiddleName + '' '','''') + isnull(p.LastName,'''') as UserName,
ROW_NUMBER() OVER (ORDER BY p.id DESC) AS RowNumber
from profiles p left outer join workexperiences w on w.ProfileId=p.id
where p.allowrecruiters=1 '
if (@Date <> 0)
Begin
--set @Query = @Query + ' and LastActivityDate <''' + cast(@Date as varchar(20)) + ''''
set @Query = @Query + ' and LastActivityDate >''' + cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20)) + ''''
End
--if (@Name <> '')
--Begin
--set @Query = @Query + ' and contains(dc.doccontent,''' + @Name1 + ''') '
--End
if (@Position <> '')
Begin
if (@Phrase = 1)
Begin
set @Query = @Query + ' and contains(w.position,''' + @Position + ''') '
end
else if (@Phrase = 2)
Begin
set @Query = @Query + ' and contains(w.position,''' + @Position + ''') '
end
else if (@Phrase = 3)
Begin
set @Query = @Query + ' and contains(w.position,''' + @Position + ''') '
end
else if (@Phrase = 4)
Begin
set @Query = @Query + ' and w.position = ''' + cast(@Position as varchar(100)) + ''''
end
End
set @Query = @Query + '),
Resumes AS
(
SELECT p.id,
p.UserName,
P.InputDate,
substring(cast(InputDate as varchar(20)),1,12) as newdate,
isnull((select c.[Name] + '', '' from cities c where c.id=p.cityid),'''') +
isnull((select r.[abbreviatedName] from regions r where r.id=p.regionid),'''') as Location,
--(select sum(endyear - startyear) from workexperiences w where w.ProfileId=p.id) as Experience,
experience,
--p.Position,
--p.Position as Title,
case when len(p.Position) < 30 then p.Position
else
substring(p.Position,1,30) + ''...'' end
as Position,
Position as Title,
degree,
--(select w.Name from AcademicExperienceTypes w where w.Id=p.AcademicExperienceTypeID) as Degree,
--cast((select s.value from salaries s where p.SalaryMinID = s.id) as int) as Salary,
cast((select s.value from salaries s where p.SalaryMaxID = s.id) as int) as MaxSalary,
salary,
--(select i.name from industries i where p.IndustryID = i.id) as Industry,
industry,
p.HomePhone,
p.CellPhone,
p.WorkPhone,
p.PrimaryEmailAddress,
p.SecondaryEmailAddress,
ROW_NUMBER() OVER (ORDER BY p.id DESC) AS RowNumber
FROM Res p
WHERE 1=1 '
if (@Stateid <>0)
Begin
set @Query = @Query + ' and p.regionid =' + cast(@Stateid as varchar(10))
End
if (@CityId <>0)
Begin
set @Query = @Query + ' and p.cityid =' + cast(@CityId as varchar(10))
End
if (@Industryid <>0)
Begin
set @Query = @Query + ' and Industryianname = Industry'
End
--if (@Industryid <>0)
--Begin
--set @Query = @Query + ' and IndustryianId =''' + @Industryid + ''') '
----set @Query = @Query + ' and contains(w.position,''' + @Position + ''') '
--End
if (@Degree <> 0)
Begin
set @Query = @Query + ' and p.Degree1id =' + cast(@Degree as varchar(10))
End
if (@Degree <> 0)
Begin
set @Query = @Query + ' and p.Degree2id =' + cast(@Degree as varchar(10))
End
if (@Degree <> 0)
Begin
set @Query = @Query + ' and p.Degree3id =' + cast(@Degree as varchar(10))
End
if (@Date <> 0)
Begin
--set @Query = @Query + ' and LastActivityDate <''' + cast(@Date as varchar(20)) + ''''
set @Query = @Query + ' and p.InputDate >''' + cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20)) + ''''
--set @Query = @Query + ' and p.InputDate >= p.IanDate '
End
if (@ExpectedSalary <> 0)
Begin
set @Query = @Query + ' and p.SalaryMinID >= ' + cast(@ExpectedSalary as varchar(10))
End
if (@ExpectedMaxSalary <> 0)
Begin
set @Query = @Query + ' and p.SalaryMaxID <= ' + cast(@ExpectedMaxSalary as varchar(10))
End
set @Query = @Query + ')
select
id,
--UserName,
case
when len(salary) < 1 then ''n/a''
else
salary
end as salary,
case
when len(degree) < 1 then ''n/a''
else
degree
end as degree,
--case when len(UserName) < 40 then
--UserName
--else
--substring(UserName,1,40) + ''...'' end
--as UserName,
case
when username like ''%email%'' then ''n/a''
else
username
end as username,
case
when len(primaryemailaddress) < 1 then ''n/a''
else
primaryemailaddress
end as primaryemailaddress,
InputDate,
newdate,
Location,
Experience,
--Position,
case
when len(position) between 1 and 30 then position
when len(position) < 1 then ''n/a''
else
substring(position,1,30) + ''...'' end
as position,
case
when len(title) between 1 and 30 then title
when len(title) < 1 then ''n/a''
else
substring(title,1,30) + ''...'' end
as title,
--case when len(Position) < 30 then
--Position
--else
--substring(Position,1,30) end
--as Position,
--Title,
--Degree,
--Salary,
MaxSalary,
Industry,
HomePhone,
CellPhone,
WorkPhone,
--PrimaryEmailAddress,
SecondaryEmailAddress,
RowNumber,
--*,
(select max(rownumber) from Resumes) as TotalCount
from Resumes
WHERE RowNumber BETWEEN ' + cast(@fromRec as varchar(10)) + ' AND ' + cast(@toRec as varchar(10)) +
'
ORDER BY RowNumber ASC
--order by LastActivityDate DESC
End
'
exec (@Query)
February 10, 2012 at 9:22 am
Fixed it 🙂
(select top 1 i.name from industries i where i.id = ' + CONVERT(varchar(15), @Industryid) + ' ) as Industryianname,
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply