asked on

A cursor with the name 'crsSelectParam' already exists.

Dear Experts,
The following stored procedure generates the following error messages:
Msg 16915, Level 16, State 1, Procedure SearchMatchedResult, Line 20
A cursor with the name 'crsSelectParam' already exists.
Msg 16915, Level 16, State 1, Procedure SearchMatchedResult, Line 124
A cursor with the name 'crsMustParam' already exists.
Msg 16915, Level 16, State 1, Procedure SearchMatchedResult, Line 195
A cursor with the name 'crsTableName' already exists.

--Code of Stored procedure
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter PROCEDURE SearchMatchedResult
      (@PostedJobID int)
AS
BEGIN
      SET NOCOUNT ON;

      DECLARE @Criteria VARCHAR(50)
      DECLARE @CriteriaValue VARCHAR(50)
      DECLARE @JobCategory VARCHAR(6)
      DECLARE @Sql VARCHAR(2000)
      DECLARE @WhereClause VARCHAR(1000)
      DECLARE @SelectClause VARCHAR(1000)

      SET @SelectClause =''
      SET @WhereClause =''
      SET @Sql =''

      DECLARE crsSelectParam CURSOR FOR
(
SELECT rtrim(ltrim(Criteria)) 'Criteria', rtrim(ltrim(CriteriaValue)) 'CriteriaValue'
            FROM PostedJobSearchParam WHERE PostedJobId = @PostedJobID
)

      OPEN crsSelectParam
            FETCH NEXT FROM crsSelectParam INTO @Criteria, @CriteriaValue
            WHILE @@FETCH_STATUS = 0

BEGIN
                  IF @Criteria = 'Experience'
                        BEGIN
                              SET @SelectClause = ltrim(rtrim(@SelectClause)) + 'Experience'
                        END
                  IF @Criteria = 'Education'
                        BEGIN
                              SET @SelectClause = ltrim(rtrim(@SelectClause)) + 'Education'
                        END
                  IF @Criteria = 'Major'
                        BEGIN
                              SET @SelectClause = ltrim(rtrim(@SelectClause)) + 'Major'
                        END
                  IF @Criteria = 'Class'
                        BEGIN
                              SET @SelectClause = ltrim(rtrim(@SelectClause)) + 'Class'
                        END
                  IF @Criteria = 'MinAge'
                        BEGIN
                              SET @SelectClause = ltrim(rtrim(@SelectClause)) + 'Age'
                        END--MinAge is must for age input. MaxAge is optional.

                  IF @Criteria = 'Gender'
                        BEGIN
                              SET @SelectClause = ltrim(rtrim(@SelectClause)) + 'Gender'
                        END
                  IF @Criteria = 'MustSkill1'
                        BEGIN
                              SET @SelectClause = ltrim(rtrim(@SelectClause)) + ltrim(rtrim(@CriteriaValue))
                        END
                  IF @Criteria = 'MustSkill2'
                        BEGIN
                              SET @SelectClause = ltrim(rtrim(@SelectClause)) + ltrim(rtrim(@CriteriaValue))
                        END
                  IF @Criteria = 'MustSkill3'
                        BEGIN
                              SET @SelectClause = ltrim(rtrim(@SelectClause)) + ltrim(rtrim(@CriteriaValue))
                        END
                  IF @Criteria = 'MustSkill4'
                        BEGIN
                              SET @SelectClause = ltrim(rtrim(@SelectClause)) + ltrim(rtrim(@CriteriaValue))
                        END
                  IF @Criteria = 'MustSkill5'
                        BEGIN
                              SET @SelectClause = ltrim(rtrim(@SelectClause)) + ltrim(rtrim(@CriteriaValue))
                        END
                  IF @Criteria = 'Skill6'
                        BEGIN
                              SET @SelectClause = ltrim(rtrim(@SelectClause)) + ltrim(rtrim(@CriteriaValue))
                        END
                  IF @Criteria = 'Skill7'
                        BEGIN
                              SET @SelectClause = ltrim(rtrim(@SelectClause)) + ltrim(rtrim(@CriteriaValue))
                        END
                  IF @Criteria = 'Skill8'
                        BEGIN
                              SET @SelectClause = ltrim(rtrim(@SelectClause)) + ltrim(rtrim(@CriteriaValue))
                        END
                  IF @Criteria = 'Skill9'
                        BEGIN
                              SET @SelectClause = ltrim(rtrim(@SelectClause)) + ltrim(rtrim(@CriteriaValue))
                        END
                  IF @Criteria = 'Skill10'
                        BEGIN
                              SET @SelectClause = ltrim(rtrim(@SelectClause)) + ltrim(rtrim(@CriteriaValue))
                        END
                  IF @Criteria = 'Skill11'
                        BEGIN
                              SET @SelectClause = ltrim(rtrim(@SelectClause)) + ltrim(rtrim(@CriteriaValue))
                        END
                  IF @Criteria = 'Skill12'
                        BEGIN
                              SET @SelectClause = ltrim(rtrim(@SelectClause)) + ltrim(rtrim(@CriteriaValue))
                        END
                  IF @Criteria = 'Skill13'
                        BEGIN
                              SET @SelectClause = ltrim(rtrim(@SelectClause)) + ltrim(rtrim(@CriteriaValue))
                        END
                  IF @Criteria = 'Skill14'
                        BEGIN
                              SET @SelectClause = ltrim(rtrim(@SelectClause)) + ltrim(rtrim(@CriteriaValue))
                        END
                  IF @Criteria = 'Skill15'
                        BEGIN
                              SET @SelectClause = ltrim(rtrim(@SelectClause)) + ltrim(rtrim(@CriteriaValue))
                        END
                  IF @@FETCH_STATUS <> 0
                        BEGIN
                              SET @WhereClause = ltrim(rtrim(@WhereClause)) + ' , '
                        END
FETCH NEXT FROM crsSelectParam INTO @Criteria, @CriteriaValue
END
      CLOSE crsSelectParam

      DECLARE crsMustParam CURSOR FOR
(
SELECT rtrim(ltrim(Criteria)) 'Criteria', rtrim(ltrim(CriteriaValue)) 'CriteriaValue'
            FROM PostedJobSearchParam WHERE PostedJobId = @PostedJobID AND Must = 1
)
      --//
      --****Where Clause string generation***
      OPEN crsMustParam
            FETCH NEXT FROM crsMustParam INTO @Criteria, @CriteriaValue
            WHILE @@FETCH_STATUS = 0

BEGIN
                  IF @Criteria = 'Experience'
                        BEGIN
                              SET @WhereClause = ltrim(rtrim(@WhereClause)) + 'Experience = ' + ltrim(rtrim(@CriteriaValue))
                        END
                  IF @Criteria = 'Education'
                        BEGIN
                              SET @WhereClause = ltrim(rtrim(@WhereClause)) + 'Education = ' + ltrim(rtrim(@CriteriaValue))
                        END
                  IF @Criteria = 'Major'
                        BEGIN
                              SET @WhereClause = ltrim(rtrim(@WhereClause)) + 'Major = ' + ltrim(rtrim(@CriteriaValue))
                        END
                  IF @Criteria = 'Class'
                        BEGIN
                              SET @WhereClause = ltrim(rtrim(@WhereClause)) + 'Class = ' + ltrim(rtrim(@CriteriaValue))
                        END
                  IF @Criteria = 'MinAge'
                        BEGIN
                              SET @WhereClause = ltrim(rtrim(@WhereClause)) + 'Age >= ' + ltrim(rtrim(@CriteriaValue))
                        END
                  IF @Criteria = 'MaxAge'
                        BEGIN
                              SET @WhereClause = ltrim(rtrim(@WhereClause)) + 'Age <= ' + ltrim(rtrim(@CriteriaValue))
                        END
                  IF @Criteria = 'Gender'
                        BEGIN
                              SET @WhereClause = ltrim(rtrim(@WhereClause)) + 'Gender = ' + ltrim(rtrim(@CriteriaValue))
                        END
                  IF @Criteria = 'MustSkill1'
                        BEGIN
                              SET @WhereClause = ltrim(rtrim(@WhereClause)) + ltrim(rtrim(@CriteriaValue)) + ' = 1'
                        END
                  IF @Criteria = 'MustSkill2'
                        BEGIN
                              SET @WhereClause = ltrim(rtrim(@WhereClause)) + ltrim(rtrim(@CriteriaValue)) + ' = 1'
                        END
                  IF @Criteria = 'MustSkill3'
                        BEGIN
                              SET @WhereClause = ltrim(rtrim(@WhereClause)) + ltrim(rtrim(@CriteriaValue)) + ' = 1'
                        END
                  IF @Criteria = 'MustSkill4'
                        BEGIN
                              SET @WhereClause = ltrim(rtrim(@WhereClause)) + ltrim(rtrim(@CriteriaValue)) + ' = 1'
                        END
                  IF @Criteria = 'MustSkill5'
                        BEGIN
                              SET @WhereClause = ltrim(rtrim(@WhereClause)) + ltrim(rtrim(@CriteriaValue)) + ' = 1'
                        END
                  IF @@FETCH_STATUS <> 0
                        BEGIN
                              SET @WhereClause = ltrim(rtrim(@WhereClause)) + ' AND '
                        END
FETCH NEXT FROM crsMustParam INTO @Criteria, @CriteriaValue
END
      CLOSE crsMustParam
      --//

      --//
      --***Table name of Job Category name***
      DECLARE crsTableName CURSOR FOR
(
SELECT JobCategory FROM PostedJob
            WHERE JobId = @PostedJobID
)

      OPEN crsTableName
            FETCH NEXT FROM crsTableName INTO @JobCategory
            WHILE @@FETCH_STATUS = 0
            BEGIN
                  IF @@FETCH_STATUS <> 0
                        BEGIN
                              SET @Sql = 'SELECT ' + ltrim(rtrim(@SelectClause)) + ' FROM ' + ltrim(rtrim(@JobCategory)) + ' WHERE ' + @WhereClause + ' ORDER BY SeekerId'
                        END
                  FETCH NEXT FROM crsTableName INTO @JobCategory
END
      CLOSE crsTableName
      --//
      print @Sql

      --//
      --***Table name of Job Category name***
      DECLARE crsResult CURSOR FOR
(
@Sql
)

      OPEN crsResult
            FETCH NEXT FROM crsResult INTO @JobCategory
            WHILE @@FETCH_STATUS = 0
            BEGIN

                  IF @@FETCH_STATUS <> 0
                        BEGIN
                              SET @Sql = 'SELECT ' + ltrim(rtrim(@SelectClause)) + ' FROM ' + ltrim(rtrim(@JobCategory)) + ' WHERE ' + @WhereClause + ' ORDER BY SeekerId'
                        END
                  FETCH NEXT FROM crsTableName INTO @JobCategory
END
      CLOSE crsTableName
      --//

END
GO

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

raju1

ASKER

Thanks.
See the following code, i don't know how many column there. Is it possible?

DECLARE crsResult CURSOR FOR
(
SELECT + @Sql
)

      OPEN crsResult
            FETCH NEXT FROM crsResult INTO @JobCategory
            WHILE @@FETCH_STATUS = 0
            BEGIN

                  IF @@FETCH_STATUS <> 0
                        BEGIN
                              --Here is processing
                        END
                  FETCH NEXT FROM crsResult INTO @JobCategory
END
      CLOSE crsResult
      DEALLOCATE crsResult