Select statement
I have the following stored procedure in which gives me an error with the select statement (at the end). I am not sure how to correct it. Please let me know if you have any suggestions. Thanks
Stored Procedure
---------------------------------------------------------------------
CREATE PROCEDURE [dbo].[Usp_selTIMS_EXPT_FAD_BASIC]
@ddlOverallExpertise varchar(1000),
@ddlOverallExpertise_count int,
@ddlcond1 varchar(10),
@ddlSpecificExpertise varchar(1000),
@ddlSpecificExpertise_count int,
@ddlcond2 varchar(10),
@ddlLanguage varchar(1000),
@ddlLanguage_count int,
@ddlLangCond varchar(10),
@ddlFluency varchar(1000),
@ddlFluency_count int,
@ddlNationality varchar(1000),
@ddlNationality_count int,
@ddlCondNation varchar(1000),
@ddlLastUsed varchar(1000),
@ddlLastUsed_count int,
@ddlStatus varchar(1000),
@ddlStatus_count int
AS
set nocount on
declare @separator char(1)
declare @separator_position int
declare @OverallExpertise varchar(1000)
declare @SpecificExpertise varchar(1000)
declare @Language varchar(1000)
declare @strSelect varchar(5000)
declare @strWhere varchar(8000)
declare @strWhere1 varchar(8000)
declare @strWhere2 varchar(8000)
declare @strWhere3 varchar(8000)
declare @strWhere4 varchar(8000)
declare @strWhere5 varchar(8000)
declare @strWhere6 varchar(8000)
declare @blnFirstParameter int
set @separator = ','
set @ddlOverallExpertise = @ddlOverallExpertise + @separator
set @ddlSpecificExpertise = @ddlSpecificExpertise + @separator
set @ddlLanguage = @ddlLanguage + @separator
set @blnFirstParameter = 1
set @strWhere = ''
set @strWhere1 = ''
set @strWhere2 = ''
set @strWhere3 = ''
set @strWhere4 = ''
set @strWhere5 = ''
set @strWhere6 = ''
if @ddlOverallExpertise_count > 0
begin
while patindex('%' + @separator + '%', @ddlOverallExpertise) <> 0
begin
select @separator_position = patindex('%' + @separator + '%', @ddlOverallExpertise)
select @OverallExpertise = left(@ddlOverallExpertise, @separator_position - 1)
-- processing array value
if @ddlOverallExpertise_count = 1
begin
set @strWhere = @strWhere + ' TIMS_FAD_EXPT_BASIC.emplid IN '
set @strWhere = @strWhere + ' ( SELECT TIMS_FAD_EXPT_XPTIZ.emplid from TIMS_FAD_EXPT_XPTIZ WHERE
TIMS_FAD_EXPT_XPTIZ.TIMS_ovr_expertise ' + @ddlcond1 + ' ' + @OverallExpertise + ')'
end
select @ddlOverallExpertise = stuff(@ddlOverallExpertise, 1, @separator_position, '')
end
end
.
.
. (similar code like above .... assigns @strWhere1,@strWhere2 ...)
.
.
.
SELECT * FROM TIMS_FAD_EXPT_BASIC WHERE + @strWhere + @strWhere1 + @strWhere2 + @strWhere3 + @strWhere4 + @strWhere5 + @strWhere6 + ORDER BY TIMS_FAD_EXPT_BASIC.NAME
set nocount off
GO
|
