• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

variables in Where clause

I am trying to build a query in an sp based on different variables.

I am getting an error:
An expression of non-boolean type specified in a context where a condition is expected, near '@LanguageCombination'.

What is wrong with the query below?

DECLARE @trad_commandesID INT;
DECLARE @SourceLanguage INT;
DECLARE @TargetLanguage INT;

SET @trad_commandesID = 8020;
SET @SourceLanguage = NULL;
SET @TargetLanguage = 1;

DECLARE @LanguageCombination NVARCHAR(100)

If COALESCE(@SourceLanguage, @TargetLanguage)  = @TargetLanguage  
SET @LanguageCombination = '(langue_cible = @TargetLanguage) '
Else
SET @LanguageCombination = '(langue_cible = @TargetLanguage) AND (langue_source = @SourceLanguage)'

SELECT     trad_commandesID, langue_source, langue_cible
FROM         tbltrad_commandes_details AS T
WHERE     (trad_commandesID = @trad_commandesID) AND @LanguageCombination

Open in new window

0
Shawn
Asked:
Shawn
  • 5
  • 3
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>WHERE     (trad_commandesID = @trad_commandesID) AND @LanguageCombination

@LanguageCombination  ... what?  You don't have a complete expression here.

Something like...

@LanguageCombination = 0
@LanguageCombination IS NULL
@LanguageCombination  = 'Goo foo boo'
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Shy of doing this as dynamic SQL, give this a whirl..

If COALESCE(@SourceLanguage, @TargetLanguage)  = @TargetLanguage  
SET @LanguageCombination = '(langue_cible = @TargetLanguage) '
Else
SET @LanguageCombination = '(langue_cible = @TargetLanguage) AND (langue_source = @SourceLanguage)'

SELECT     trad_commandesID, langue_source, langue_cible
FROM         tbltrad_commandes_details AS T
WHERE     (trad_commandesID = @trad_commandesID) AND
    CASE WHEN COALESCE(@SourceLanguage, @TargetLanguage) =  @TargetLanguage  THEN langue_cible = @TargetLanguage
   ELSE (langue_cible = @TargetLanguage AND langue_source = @SourceLanguage) END
0
 
ShawnAuthor Commented:
>> @LanguageCombination  ... what?  You don't have a complete expression here.

yes. see lines 12 and 14 in the above code
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
ShawnAuthor Commented:
now you're going to say I can't do that, right. :(
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
you need dynamic sql here

DECLARE @trad_commandesID INT;
DECLARE @SourceLanguage INT;
DECLARE @TargetLanguage INT;

SET @trad_commandesID = 8020;
SET @SourceLanguage = NULL;
SET @TargetLanguage = 1;

DECLARE @LanguageCombination NVARCHAR(100)

If COALESCE(@SourceLanguage, @TargetLanguage)  = @TargetLanguage  
SET @LanguageCombination = '(langue_cible = @TargetLanguage) '
Else
SET @LanguageCombination = '(langue_cible = @TargetLanguage) AND (langue_source = @SourceLanguage)'

declare @nSQL nvarchar(1000)
SET  @nSQL = 'SELECT     trad_commandesID, langue_source, langue_cible
FROM         tbltrad_commandes_details AS T
WHERE     (trad_commandesID = @trad_commandesID) AND '+ @LanguageCombination

exec sp_ExecuteSQL @nSQL , N'@trad_commandesID int, @SourceLanguage int,@TargetLanguage int ', @trad_commandesID,@SourceLanguage,@TargetLanguage
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<slight correction, didn't need the IF block anymore>

SELECT     trad_commandesID, langue_source, langue_cible
FROM         tbltrad_commandes_details AS T
WHERE     (trad_commandesID = @trad_commandesID) AND
    CASE
      WHEN COALESCE(@SourceLanguage, @TargetLanguage) =  @TargetLanguage  THEN langue_cible = @TargetLanguage
   ELSE (langue_cible = @TargetLanguage AND langue_source = @SourceLanguage) END
0
 
ShawnAuthor Commented:
jimhorn...looks good though getting
Incorrect syntax near '='.
0
 
ShawnAuthor Commented:
any idea why this isn't working?
0
 
ShawnAuthor Commented:
aneeshattingal
just tried your dynamic query solution and it works perfect. thank you

I may have another question soo as this query is actually part of a bigger sp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now