[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

variables in Where clause

Posted on 2012-09-11
9
Medium Priority
?
264 Views
Last Modified: 2012-09-11
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
Comment
Question by:Shawn
  • 5
  • 3
9 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38388853
>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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38388860
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
 
LVL 1

Author Comment

by:Shawn
ID: 38388866
>> @LanguageCombination  ... what?  You don't have a complete expression here.

yes. see lines 12 and 14 in the above code
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 1

Author Comment

by:Shawn
ID: 38388867
now you're going to say I can't do that, right. :(
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 38388872
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38388875
<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
 
LVL 1

Author Comment

by:Shawn
ID: 38388891
jimhorn...looks good though getting
Incorrect syntax near '='.
0
 
LVL 1

Author Comment

by:Shawn
ID: 38388946
any idea why this isn't working?
0
 
LVL 1

Author Comment

by:Shawn
ID: 38388987
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question