Issue with declaring SQL In local variables

Question

Hello everyone,

I'd like to declare two local variables and assign values to them (Highlighted). However the the error I'm getting is telling me, quite rightly, that I have Invalid column names, this is because I don't shorten the column names with AS until further down the script.

Is there a way round this or do I have to define my tables explicitly whilst declaring the variables.

Any suggestions would be greatly appreciated, as always.

 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:        Alan Coleman
-- Create date: 1 April 2011   
-- Description:    Compare CourseURL against course against tblCourseTitle to see whether the URL is a course
-- =============================================
ALTER PROCEDURE [dbo].[fbt_course_id]
    -- Add the parameters for the stored procedure here
    (
        @altLangIntLanguageID INT,
        @rewriteUrl VARCHAR(100)
    )
AS
BEGIN

DECLARE    @loggedVarTrue    VARCHAR(100)
DECLARE    @loggedVarFalse    VARCHAR(100)

SET        @loggedVarTrue    = "(c.CourseStatus = 1 OR c.CourseStatus = 2) AND (t.TitleStatus = 1 OR t.TitleStatus = 2)"
SET        @loggedVarFalse    = "c.CourseStatus = 1 AND t.TitleStatus = 1"

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT
        t.CourseID,
        c.CourseStatus
    FROM
        tblCourseTitle AS t,
        tblFbtCourses AS c
    WHERE
        LanguageID = @altLangIntLanguageID
        AND CourseURL = @rewriteUrl
        AND t.CourseID = c.CourseID
        --AND @loggedVarTrue
END

 

---------

Error:

Msg 207, Level 16, State 1, Procedure fbt_course_id, Line 19
Invalid column name '(c.CourseStatus = 1 OR c.CourseStatus = 2) AND (t.TitleStatus = 1 OR t.TitleStatus = 2)'.
Msg 207, Level 16, State 1, Procedure fbt_course_id, Line 20
Invalid column name 'c.CourseStatus = 1 AND t.TitleStatus = 1'.

Answer 1

replace with following in above code.

SET        @loggedVarTrue    = '(c.CourseStatus = 1 OR c.CourseStatus = 2) AND (t.TitleStatus = 1 OR t.TitleStatus = 2)'
SET        @loggedVarFalse    = 'c.CourseStatus = 1 AND t.TitleStatus = 1'

---

Cheers Sunil Gure

Answer 2

Hi,

I think, you need to use dynamic sql to do this..

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:    Alan Coleman
-- Create date: 1 April 2011  
-- Description:  Compare CourseURL against course against tblCourseTitle to see whether the URL is a course
-- =============================================
ALTER PROCEDURE [dbo].[fbt_course_id]
  -- Add the parameters for the stored procedure here
  (
    @altLangIntLanguageID INT,
    @rewriteUrl VARCHAR(100)
  )
AS
BEGIN

DECLARE  @loggedVarTrue  VARCHAR(100)
DECLARE  @loggedVarFalse  VARCHAR(100)

SET    @loggedVarTrue  = "(c.CourseStatus = 1 OR c.CourseStatus = 2) AND (t.TitleStatus = 1 OR t.TitleStatus = 2)"
SET    @loggedVarFalse  = "c.CourseStatus = 1 AND t.TitleStatus = 1"

  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  -- Insert statements for procedure here
Declare @sql nvarchar(4000)  
select @sql = '
  SELECT 
    t.CourseID, 
    c.CourseStatus 
  FROM 
    tblCourseTitle AS t, 
    tblFbtCourses AS c 
  WHERE 
    LanguageID = @altLangIntLanguageID 
    AND CourseURL = @rewriteUrl
    AND t.CourseID = c.CourseID '
    + @loggedVarTrue

exec sp_executesql @sql
    
END

Ideally, you shoud not set QUOTED_IDENTIFIER ON, and then you need to use single quote(') in place of double quote(") in above code

---

- Chintak (My Blog)

Answer 3

Thanks Sunil,

That sorts the problem further up but now I'm getting an error when I try to bring the variable into into the Query:

Msg 4145, Level 15, State 1, Procedure fbt_course_id, Line 39
An expression of non-boolean type specified in a context where a condition is expected, near 'END'.

I don't think this is going to work is it? But thanks anyway.

 

 

Answer 4

Thanks Chintak,

 

although this approach means that the variables inside the @sql string are no longer picked up. Any ideas?

Answer 5

That sorts the problem further up but now I'm getting an error when I try to bring the variable into into the Query:

Msg 4145, Level 15, State 1, Procedure fbt_course_id, Line 39 An expression of non-boolean type specified in a context where a condition is expected, near 'END'.

Yes, that is because you have:

     WHERE LanguageID = @altLangIntLanguageID
        AND CourseURL = @rewriteUrl
        AND t.CourseID = c.CourseID
        AND @loggedVarTrue

You need to compare @loggedVarTrue to something. Now you have an incomplete condition.

Judging from this

SET        @loggedVarTrue    = "(c.CourseStatus = 1 OR c.CourseStatus = 2) AND (t.TitleStatus = 1 OR t.TitleStatus = 2)"*
*SET        @loggedVarFalse    = "c.CourseStatus = 1 AND t.TitleStatus = 1"*

It seems that you intended to @loggedVarTrue to be some sort of macro expansion, but there is no such feature in SQL Server.

(And the reason you got the error message you got, is that in SQL " serves to delimit *identifiers*, to permit you have tables like Order Details. The string delimiter is single quote.)

It is not clear what you actually want to achieve. In another post it was suggested that you need dynamic SQL, but I would say that you need dynamic SQL just like you need a hole in your shoe. What you foremost need, seems to be to learn basics in SQL and dynamic SQL is certainly not a feature for beginners. Probably you should just have IF ELSE and two different queries depending on the conditions.

---

Erland Sommarskog, SQL Server MVP, [e-mail]
Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
(Just click the link you need.)

Answer 6

Thanks Erland,

 

I was going to bring either @loggedVarTrue or @loggedVarFalse into the query depending on another variable that I will carry into the procedure. Initially I just wanted to get one of them working.

The Dynamic SQL - hole in my shoe. Okay!

What I really wanted to avoid was writing the query twice, not such a big deal in this instance, but I'll be moving some big queries into SPs soon and I wanted to continue using 'Do not repeat' as much as possible.

Ironic, because with our SQL embedded in the code (Which is what we want to move away from) this is really easy!

Thanks for the help. :-)

 

Answer 7

> What I really wanted to avoid was writing the query twice, not such a big deal in this instance, but I'll be moving some big queries into SPs soon and I wanted to continue using 'Do not repeat' as much as possible.

That depends. You posted an example with two alternatives, and in that case IF is the way to do. With tons of selections, it isn't.

Anyway, on my web site there are two articles, http://www.sommarskog.se/dyn-search.html discusses how to handle dynamic search conditions in general. And http://www.sommarskog.se/dynamic_sql.html covers dynamic SQL in general. I would recommend that you study these articles. Properly implemented, dynamic search routines can be decently simple to understand and maintain. Less properly implemented, the code will be unmanageable.

---

Erland Sommarskog, SQL Server MVP, [e-mail]
Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
(Just click the link you need.)