CODEWITHSUNDEEP
sql-server-2005stored-procedures
Saghir A. Khatri
Saghir A. Khatri

Reputation: 3128

Invalid column in SQL Server 2005 stored procedure

I have just created an attribute in a table UserBasics with name SkillType.

I am trying to use SkillType (new value) in my stored procedure:

CREATE PROCEDURE SelectMentor 
    -- Add the parameters for the stored procedure here
    @Zip varchar(20) = NULL,
    @Company varchar(200) = NULL,
    @Designation varchar(100) = NULL,
    @Interest varchar(200) = NULL,
    @CurrentID varchar(200) = NULL,
    @SkillType varchar(50)  =NULL
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT 
       user_Id, user_Fullname,
       Designation, Company,user_Email
    FROM
       (SELECT
           user_Id, user_Fullname, user_Zip ,user_Need,user_Email,
           STUFF(
               (SELECT ', ' + Designation
                FROM   UserProfession
                WHERE  Prof_ID = a.user_Id
                FOR XML PATH (''))
                , 1, 1, '')  AS Designation,
           STUFF(
               (SELECT DISTINCT ', ' + Company
                FROM   UserProfession
                WHERE  Prof_ID = a.user_Id
                FOR XML PATH (''))
                , 1, 1, '')  AS Company
        FROM UserBasics AS a
        GROUP BY 
             user_Id, user_Fullname, user_Zip, user_Need, user_Email) s
   WHERE  
      (@Zip is null or user_Zip like '%'+@Zip+'%') and
      (@Interest is null or user_Need like '%'+@Interest+'%') and
      (@Company is null or Company like '%'+@Company+'%') and
      (user_Id != @CurrentID) and
      (SkillType = @SkillType) and
      (@Designation is null or Designation like '%'+@Designation+'%')
END
GO

but it says

Msg 207, Level 16, State 1, Procedure SelectMentor, Line 46
Invalid column name 'SkillType'.

I know its Cache issue, but how I may refresh cache in SQL Server 2005.

Please guide me to proper direction

Regards,

Edit

I also tried to refresh the object explorer and also tried restarting service, but error still occurs. I can see the column in table. Recreating stored procedure didn't led to me towards success too

Upvotes: 0

Views: 130

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300529

You need to include your new column, SkillType, in the select list of the sub-select:

FROM
    (
      SELECT
           user_Id, user_Fullname, user_Zip ,user_Need,user_Email, 
           SkillType   -- <-----
           STUFF(
               (SELECT ', ' + Designation
                FROM   UserProfession
                WHERE  Prof_ID = a.user_Id
                FOR XML PATH (''))
                , 1, 1, '')  AS Designation,
           STUFF(
               (SELECT DISTINCT ', ' + Company
                FROM   UserProfession
                WHERE  Prof_ID = a.user_Id
                FOR XML PATH (''))
                , 1, 1, '')  AS Company
      FROM UserBasics AS a
      GROUP BY user_Id, user_Fullname, user_Zip ,user_Need,user_Email, 
               SkillType  -- <-----
    ) s

Otherwise, you can't refer to it in your outer WHERE clause.

Upvotes: 2

Related Questions