SQL Server: Error while using TSQL variables

Question

I have a user function that returns a BIT calle dbo.IsPartReady. I am trying to use the function inside of a trigger as follows:

SET @railReady = dbo.IsPartReady(1,@curPartiId);
SET @frameReady = dbo.IsPartReady(2,@curPartiId);
SET @dryAReady = dbo.IsPartReady(3,@curPartiId);
SET @dryBReady = dbo.IsPartReady(4,@curPartiId);

IF ( (@railReady AND @frameReady ) OR ( @dryAReady AND @dryBReady ) )

I'm getting the following error in the IF statement: An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.

What am I doing wrong ?

in which way the local variables are declared? please, show the code — heximal
– heximal, Commented Jul 4, 2011 at 8:57
Unless @curPartiId is being set inside a while loop (shudder) or a cursor loop (double shudder), it's quite likely your trigger is broken for multi-row operations also. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 4, 2011 at 9:10
Agreed Damien (+1), except there isn't really much difference (so not sure why you shudder differently). A while loop is just a cursor without DECLARE CURSOR. — Aaron Bertrand
– Aaron Bertrand, Commented Jul 4, 2011 at 16:38

Mikael Eriksson · Accepted Answer · 2011-07-04 09:02:34Z

4

BIT data type in SQL Server is not a boolean it is an integer. You have to compare the value of the variable with something to get a boolean expression. BIT can have the value 0, 1 or NULL. http://msdn.microsoft.com/en-us/library/ms177603.aspx

declare @B bit = 1

if @B = 1
begin
  print 'Yes'
end

edited Jul 4, 2011 at 9:02

answered Jul 4, 2011 at 8:57

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anthony Faull · Accepted Answer · 2011-07-05 08:44:32Z

3

Use the following:

IF ((@railReady = 1 AND @frameReady = 1) OR (@dryAReady = 1 AND @dryBReady = 1))

or alternatively,

IF ((@railReady & @frameReady) | (@dryAReady & @dryBReady)) = 1

More information:

To verify this we can use a truth table containing all combinations of four bit values:

WITH B(x) AS (SELECT CAST(0 AS bit) UNION ALL SELECT CAST(1 AS bit))
, AllSixteenCombinations(a,b,c,d) AS
    (SELECT * FROM B B1 CROSS JOIN B B2 CROSS JOIN B B3 CROSS JOIN B B4)
SELECT a,b,c,d
, CASE WHEN ((a = 1 AND b = 1) OR (c = 1 AND d = 1)) THEN 'Y' ELSE 'N' END[Logic]
, CASE WHEN ((a & b) | (c & d)) = 1 THEN 'Y' ELSE 'N' END [Bitwise]
FROM AllSixteenCombinations

Output:

a     b     c     d     Logic Bitwise
----- ----- ----- ----- ----- -------
0     0     0     0     N     N
0     1     0     0     N     N
0     0     1     0     N     N
0     1     1     0     N     N
1     0     0     0     N     N
1     1     0     0     Y     Y
1     0     1     0     N     N
1     1     1     0     Y     Y
0     0     0     1     N     N
0     1     0     1     N     N
0     0     1     1     Y     Y
0     1     1     1     Y     Y
1     0     0     1     N     N
1     1     0     1     Y     Y
1     0     1     1     Y     Y
1     1     1     1     Y     Y

(16 row(s) affected)

edited Jul 5, 2011 at 8:44

answered Jul 4, 2011 at 10:01

Anthony Faull

18.1k5 gold badges58 silver badges75 bronze badges

2 Comments

Aaron Bertrand Over a year ago

For your first example, I strongly recommend always using parentheses when combining AND and OR. For the second one, I'm not sure what language this is meant to be in, but I certainly couldn't get anything similar working in T-SQL. Can you post an actual working line of code that you've tried?

t-clausen.dk Over a year ago

Worked like a charm for me in 2005, interesting + 1

Collectives™ on Stack Overflow

SQL Server: Error while using TSQL variables

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related